CODEWITHSUNDEEP
sql-serverstring
soamazing
soamazing

Reputation: 1696

Select only string with number SQL Server

I need select only strings in my table, but this table has numbers and strings together.

Ex:

ID Name
1  Jacke11
2  Andre
3  Rodrigo11
4  55555

My select need return only Ids: 1, 2, 3.

Thanks

Upvotes: 9

Views: 10810

Answers (2)

JNK
JNK

Reputation: 65147

As an alternative to Joe's very fine ISNUMERIC solution, you can use PATINDEX to make sure you have an alpha character:

SELECT ID
    FROM YourTable
    WHERE PATINDEX('%[a-z]%', name) > 0

This may be slightly faster since it will stop searching the string as soon as it gets to the first alpha character.

Upvotes: 7

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

SELECT ID
    FROM YourTable
    WHERE ISNUMERIC(Name + '.0e0') = 0

Upvotes: 18

Related Questions