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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to pull in the records with alphanumeric

Author  Topic 

treeskin
Starting Member

22 Posts

Posted - 2007-09-24 : 21:32:19
Hi,

I would like to pull in the record that contain alphanumeric

Sample data:
ItemName ItemNumber
Pen P1234
Pencil 014P6
Ruler 23453
Eraser 7D98R

Output to show only records on ItemNumber with alphanumberic:
ItemName ItemNumber
Pen P1234
Pencil 014P6
Eraser 7D98R

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-24 : 23:38:36
something like.. WHERE ITemNumber < 0 might work?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

treeskin
Starting Member

22 Posts

Posted - 2007-09-25 : 00:49:35
Hi Dinakar,

I tried your suggestion but doesn't seem working that well cuz it will cause some conversion issue. Not sure whether that is because of the hyphen so I put in the new sample data as below:

Sample data:
ItemName ItemNumber
Pen P1-2Y4
Pencil 01-4P6
Ruler 234-53
Eraser 7D9-8R

Output to show only records on ItemNumber with alphanumberic:
ItemName ItemNumber
Pen P1-2Y4
Pencil 01-4P6
Eraser 7D9-8R
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 01:25:45
select * from table1 where col1 like '%[a-z]%'

select * from table1 where col1 not like '%[^0-9-]%'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

treeskin
Starting Member

22 Posts

Posted - 2007-09-25 : 02:26:06
Hi Peso,

Yes, the 'select * from table1 where col1 like '%[a-z]%'' works for my condition.

Thank you very much...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 04:50:20
quote:
Originally posted by Peso

select * from table1 where col1 like '%[a-z]%'

select * from table1 where col1 not like '%[^0-9-]%'



E 12°55'05.25"
N 56°04'39.16"



Second would return only those having numbers and hyphen

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 04:52:37
quote:
Originally posted by dinakar

something like.. WHERE ITemNumber < 0 might work?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


That would result in error

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -