CODEWITHSUNDEEP
sqlsql-server-2008selectintnvarchar
Ehsan
Ehsan

Reputation: 3491

How select `Only String` and `Only Int`

I have some nvachar data in a my database.

aaaa , bb1b, c+cc , 1234 , dser , 5896

I need to two select for Only String and Only Int. This is mean :

Result of first select be : aaaa , dser

Result of second select be : 1234 , 5896

How?

Upvotes: 0

Views: 5123

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

You can use LIKE to perform this comparison. By using a double-negative, you can perform the tests with a single expression each:

declare @t table (val varchar(10) not null)
insert into @t(val)
select 'aaaa' union all
select 'bb1b' union all
select 'c+cc' union all
select '1234' union all
select 'dser' union all
select '5896'

select * from @t where val not like '%[^A-Z]%'
select * from @t where val not like '%[^0-9]%'

The first select says "give me all strings that don't contain a non-alphabetic character". Similarly, the second says "give me all strings that don't contain a non-digit"

Upvotes: 3

jschildgen
jschildgen

Reputation: 4199

You need to check for a regular expression.

In your example the query would look like this:

SELECT * FROM example WHERE foo LIKE '%[0-9]%' AND foo NOT LIKE '%[A-Z]%'

and

SELECT * FROM example WHERE foo NOT LIKE '%[0-9]%' AND foo LIKE '%[A-Z]%'

Maybe you have to do UPPERCASE(foo) for case insensitive checks. And add other characters like +, - and so on to the NOT LIKE expression.

See: http://msdn.microsoft.com/en-us/library/ms187489(SQL.90).aspx

Upvotes: 2

Related Questions