Select Query - From String Values...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query - From String Values...?

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2012-01-29 : 10:26:49

Iam using sql server 2008. I have a EmplyeeTable and the field Employee_No contains, Numbers & Letters like the below..

01. But I want to select the filed of Employee_No contains,Numbers
02. Also I want to select the field of Employee_No contains,Letters

EmployeeTable.Employee_No
-------------------------
1
2
32
712
5
45
9000
324
abcd
a
bew2
etg
cf2
f
d3
cxv
rda

My Query
--------
01. select * from EmployeeTable where Employee_No like ????

Thanks

Paramu @ PARANTHAMAN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-29 : 10:36:52

You can use like clause for example this will return rows where there are no numbers in the Employee_No:


select * from EmployeeTable where Employee_No not like '%[0-9]%'

If you want rows that have at least one number in the employee_no, you can use this:


select * from EmployeeTable where Employee_No like '%[0-9]%'

For letters you can use '%[a-zA-Z]%'

paramu
Posting Yak Master

151 Posts

Posted - 2012-01-29 : 10:45:11

Thanksyou Sunitabeck..But the problem is, its showing Letters included with numbers also... But I need Only Nnumbers and Only Letters

The O/p Like the below..
1,2,32,712,5,45,9000,324,......bew2,cf2,d3 -----> this Number & Letter is problem..

Thanks

Paramu @ PARANTHAMAN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-29 : 11:16:27

If you want ONLY numbers (i.e., exclude letters):

SELECT * FROM #tmp WHERE Employee_No NOT LIKE '%[a-zA-Z]%'

If you want ONLY letters (i.e., exclude numbers):

SELECT * FROM #tmp WHERE Employee_No NOT LIKE '%[0-9]%'

If there may be other characters, such as under_score, currency symbols etc., you would need to include those also in the NOT LIKE clause.

If this is not what you are looking for, can you post the sample output you want to get for the example you posted in your initial post?

paramu
Posting Yak Master

151 Posts

Posted - 2012-01-30 : 01:31:47

Thankyou Sunitabeck..I got it.

Paramu @ PARANTHAMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources