I have something like that
SELECT InterfaceAlias,CircuitID,
FROM Interfaces
WHERE CircuitID LIKE '%' AND InterfaceAlias LIKE '%CID%';
Is there a way to find CircuitID number that is not mach part of the Interface name
like that .
If you're wanting to find This:
CID34000 Shaynet 1000000
But not this:
CID34000 Shaynet 34000
Then you'll want to do something like this:
Select InterfaceAlias, CircuitID
Where CircuitID is not null
AND Patindex('%' + CircuitID + '%' ,InterfaceAlias) = 0
You may need to cast/convert CircuitID depending on datatype....
You can also add other clauses to narrow down your search at your preference such as:
AND InterfaceAlias LIKE '%CID% (from your original query)
I'm doing this off the top of my head, so caveate query-or:
WHERE CircuitID is not null
AND InterfaceAlias LIKE '%CID%';
AND InterfaceAlias NOT LIKE interfaces.CircuitID
I've never considered using syntax like that before, but it works. You do have to concatenate '%' + interfaces.CircuitID + '%' though.
It would seem more obvious than my choice, but I do a TON of substringing and text manipulation in my scripts and procs so Patindex is a common occurrence and I'm usually testing for 0 cases prior to using it as a starting point for a substring.
If you're searching on a non-indexed field Patindex is faster.
that's awesome. Thanks for the tip!
You're welcome and thank you too. I learned as much if not more from your post. I forget sometimes there are usually more ways of accomplishing the same goal. And I thought for sure that your way was faster until I decided to test it. They both perform the same unless the field isn't indexed.
Great Acy
That did it :-) Cool
I thanks you and adatole 2 for getting into the problem
/SJA
