Select only string with number SQL Server

Question

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

Eh, is id really a separate column and name a separate column? — Joachim Isaksson
– Joachim Isaksson, Commented Jan 12, 2012 at 18:18
if your select needs to return only 1, 2, 3, you need only integers from the table. Is separated by spaces? — Gustavo F
– Gustavo F, Commented Jan 12, 2012 at 18:18

Joe Stefanelli · Accepted Answer · 2012-01-12 18:18:29Z

18

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

answered Jan 12, 2012 at 18:18

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yuck Over a year ago

I think I know why, but you can comment on why you're doing + '.0e0' as I don't think it's very intuitive to need that.

JNK Over a year ago

@Yuck - Because you can get unexpected matches with ISNUMERIC since it will evaluate TRUE for non-int datatypes like float (also money and some symbols). 12e10 will pass ISNUMERIC as TRUE. By adding the .0e0 you force it to check only for integer values.

Joe Stefanelli Over a year ago

@Yuck: Without that ISNUMERIC will return a 1 for such things as '+' and '$'.

Henryk Budzinski Over a year ago

Using ISNUMERIC for this will cause a lot more trouble. The most correct and performative answer at the moment is @JNK answer.

JNK · Accepted Answer · 2012-01-12 18:27:03Z

7

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.

answered Jan 12, 2012 at 18:27

JNK

65.5k15 gold badges127 silver badges138 bronze badges

1 Comment

Henryk Budzinski Over a year ago

to avoid an exception I would put a COALESCE(name, '') or something similar

Collectives™ on Stack Overflow

Select only string with number SQL Server

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related