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 |
|
simpleton
Starting Member
25 Posts |
 Posted - 2008-03-28 : 08:59:50
|
| Guys,I need to cleanup a query on Item Numbers.I don't want to edit the data... just display it clean.I want to show ItemNumbers without any 'S' on the left, and without any '501' or '601' on the right. What do you think is my best bet fot getting this?I was thinking about using TRIM, but maybe that's just for whitespace. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-28 : 09:01:19
|
Do all ItemNumbers have "S" on the left?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-28 : 09:02:29
|
[code]SELECT CASE WHEN ItemNumber LIKE 'S%[56]01' THEN SUBSTRING(ItemNumber, 2, LEN(ItemNumber) - 4) WHEN ItemNumber LIKE '%[56]01' THEN SUBSTRING(ItemNumber, 1, LEN(ItemNumber) - 3) WHEN ItemNumber LIKE 'S%' THEN SUBSTRING(ItemNumber, 2, LEN(ItemNumber) - 1) ELSE ItemNumberFROM Table1[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
simpleton
Starting Member
25 Posts |
Posted - 2008-03-28 : 12:40:10
|
| Awesome.That's perfect. Thanks!! |
|
|
|
|
|
|
trim data from left and right