July 14, 2009 at 3:14 pm
hi guys,
I have a coulmn in a table which has some bad data.We basically got the bad data after cunvertiong from an older system.I need to remove the chanrecters precceding a space including the the space .
eg if the data in the column is ABC 12345 i need to make it 12345.The length of the string can be diff in each row and also the occurence of the space.ISsthere a way i can cleanup the data?
thanks in advance.
July 14, 2009 at 3:46 pm
This will work as long as you always want to keep everything to the right of the first occurance of a space in the data:
DECLARE @TABLE TABLE(RowValue varchar(20))
INSERT INTO @Table
SELECT 'ABC 12345' UNION ALL
SELECT 'DEDF 3215613' UNION ALL
SELECT 'sd 84321'
SELECT * FROM @Table
UPDATE @Table
SET RowValue = LTRIM(SUBSTRING(RowValue,CHARINDEX(' ',RowValue),LEN(RowValue)))
SELECT * FROM @Table
July 15, 2009 at 10:37 am
Thank you john ,It seems to be working in most cases but it is also taking away the last charecter on the right side when it value should not be modified .It works fine when there is a apace and precceding chanrecters.
July 15, 2009 at 10:43 am
Greetings,
Try changing one line to this:
SET RowValue = LTRIM(SUBSTRING(RowValue, CHARINDEX(' ', RowValue), LEN(RowValue) + 1))
The SUBSTRING is boundary safe so giving it a value that is greater than the actual length will not break anything. It will also let it pick up the last character when CHARINDEX returns a 0 instead of a positional value.
Have a good day.
Terry Steadman
July 15, 2009 at 12:54 pm
thx terry .it worked
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply