T-SQL Pattern Matching
I am trying to return records where the first three characters in the column start with numbers only. If it has four numbers, I want it to skip. Any thoughts?
Example:
234 <--returned in results
323 <--returned in results
2345 <- NOT returned in results
234H <--returned in results
265 <--returned in results
543 T <--returned in results
Example:
234 <--returned in results
323 <--returned in results
2345 <- NOT returned in results
234H <--returned in results
265 <--returned in results
543 T <--returned in results
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT * FROM <TableName>
WHERE <ColumnName> like '[0-9][0-9][0-9][^0-9]%'
WHERE <ColumnName> like '[0-9][0-9][0-9][^0-9]%'
ASKER
KnightEknight and Sharath, thank you both for your input. Cutting and pasting each of your inputs worked.
I want to give a shout-out to TempDBA's solution as well. I believe his is the most efficient because it does not use in-line functions, and it may allow the optimizer to make use of an index (if it exists) on the column (under ideal conditions anyway).
To clarify, the index could be used if you also include additional conditions on the column like this:
SELECT * FROM <TableName>
WHERE <ColumnName> like '[0-9][0-9][0-9][^0-9]%'
and <ColumnName> >= '000'
and <ColumnName> <= '999'
SELECT * FROM <TableName>
WHERE <ColumnName> like '[0-9][0-9][0-9][^0-9]%'
and <ColumnName> >= '000'
and <ColumnName> <= '999'
where isnumeric(left(myColumn,3)
and isnumeric(left(myColumn,4)