March 2, 2012 at 12:29 pm
Having trouble writing an Update query to cleanup(strip off) unwanted data on nvarchar(32). Table has about 8900 rows.
Want to strip off 'TG:' in TagNumber & 'SN:' in SerialNumber. Also want to strip off any spaces between the ':' & the next non-blank char., there can be none,1,2 spaces after the ':'. If everything past the ':' is BLANK then the updated column is BLANK.
I have the query to select only those rows I want to modify but don't know how to update a column within itself.
CREATE TABLE TestInventory
(
KeyID int,
TagNumber nvarchar(32),
SerialNumber nvarchar(32)
);
GO
Insert TestInventory
(KeyID, TagNumber, SerialNumber)
values
(5958,'HS396','SN: 4H54212PSEB'),
(5959,'HS397','SN: 4H542121SEB'),
(5960,'TG: L91L','SN: 36056350'),
(6925,'SO5','SN: 29701771'),
(8313,'100159L','SN: 1801157'),
(4820,'CO97','SN:'),
(7547,'90306','SN: 8180748'),
(8665,'AV110139','13971111101260QHB'),
(692,'R39','SN:492232164497-'),
(8058,'80851','SN: SCNU8401V1Z'),
(5964,'TG: A97','SN: 35964410'),
(5986,'TG: H124','SN: 35934432'),
(5987,'TG: H125','SN: 35934431')
;
GO
select KeyID,
TagNumber, SerialNumber
from TestInventory
where TagNumber like 'TG:%'
or SerialNumber like 'SN:%'
Desired results:
KeyIDTagNumberSerialNumber
5958HS3964H54212PSEB
5959HS3974H542121SEB
5960L91L36056350
6925SO529701771
8313100159L1801157
4820CO97
7547903068180748
692R39492232164497-
805880851SCNU8401V1Z
5964A9735964410
5986H12435934432
5987H12535934431
Not sure what to use to remove the unwanted data.
Thanks
March 2, 2012 at 12:41 pm
Thanks for posting clean ddl and sample data...goes a long way!!!
I am not totally clear if you wanted the literal "BLANK" or an empty string so I wrote it both ways.
This is the empty string version.
select KeyID, TagNumber, ltrim(rtrim(REPLACE(TagNumber, 'TG:', ''))) as TrimmedTagNumber,
SerialNumber, ltrim(rtrim(REPLACE(SerialNumber, 'SN:', ''))) as TrimmedSerialNumber
from TestInventory
Here is the literal BLANK version.
select KeyID, TagNumber,
case when ltrim(rtrim(REPLACE(TagNumber, 'TG:', ''))) = '' then 'BLANK' else ltrim(rtrim(REPLACE(TagNumber, 'TG:', ''))) end as TrimmedTagNumberWithLiteralBLANK,
SerialNumber,
case when ltrim(rtrim(REPLACE(SerialNumber, 'SN:', ''))) = '' then 'BLANK' else ltrim(rtrim(REPLACE(SerialNumber, 'SN:', ''))) end as TrimmedSerialNumberWithLiteralBLANK
from TestInventory
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2012 at 12:48 pm
I wanted the empty string.
Thanks this works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply