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.
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 ItemNumberPen P1234Pencil 014P6Ruler 23453Eraser 7D98ROutput to show only records on ItemNumber with alphanumberic:ItemName ItemNumberPen P1234Pencil 014P6Eraser 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/ |
|
|
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 ItemNumberPen P1-2Y4Pencil 01-4P6Ruler 234-53Eraser 7D9-8ROutput to show only records on ItemNumber with alphanumberic:ItemName ItemNumberPen P1-2Y4Pencil 01-4P6Eraser 7D9-8R |
|
|
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" |
|
|
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... |
|
|
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 hyphenMadhivananFailing to plan is Planning to fail |
|
|
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 MadhivananFailing to plan is Planning to fail |
|
|
|
|
|