June 10, 2010 at 9:17 am
Hello Experts,
I have a table with one column. Data in this column are like,
;abce;
;kakrms;lameje;
;ackdlakm;acd;acddr;
;zl;
I want to update this and make it as below,
abce
kakrms;lameje
ackdlakm;acd;acddr
zl
Basically, remove semicolon from front and end. Semicolon should exist between two words in one record.
June 10, 2010 at 9:29 am
I came up with something like this,
declare @string varchar(100), @len1 int, @new varchar(100), @lenfinal int, @newfinal varchar(100)
set @string = ';ABCD;EFGHIJ;'
select @len1 = LEN(@string)
set @new = LEFT(@string, @len1 - 1)
print @new
select @lenfinal = LEN(@new)
set @newfinal = RIGHT (@new, @lenfinal -1)
print @newfinal
but, I am sure this is very basic as per my knowledge. Can anyone suggest anything else?
June 10, 2010 at 9:34 am
You better to check if the value has ';' character in front and at the end. So I suggest the following (together with table and data setup, which you better provide when asking your question):
create table #MyTable (col1 varchar(50))
insert into #MyTable
select ';abce;'
union select ';kakrms;lameje;'
union select ';ackdlakm;acd;acddr;'
union select ';zl;'
union select 'fghhg;asdasd;'
union select ';fghhgaa;asdasdsdgas'
union select 'asda;sdf'
select * from #MyTable
-- UPDATE YOU NEED:
update #MyTable set col1 = case when LEFT(col1, 1) = ';' AND RIGHT(col1, 1) = ';' then substring(col1,2, len(col1) - 2)
when LEFT(col1, 1) = ';' then substring(col1,2,len(col1))
when RIGHT(col1, 1) = ';' then substring(col1,1, len(col1) - 1)
else col1
end
select * from #MyTable
June 10, 2010 at 9:38 am
i think something like this would be better: it's set based, so it's fast, and if the data doesn't start with the offending char, it's not adversly affected:
--remove preceeding semi colon if it exists:
UPDATE SOMETABLE
SET SOMECOLUMN = SUBSTRING(SOMECOLUMN ,2,LEN(SOMECOLUMN ))
WHERE LEFT(SOMECOLUMN,1) = ';'
--remove ending semi colon if it exists:
UPDATE SOMETABLE
SET SOMECOLUMN = SUBSTRING(SOMECOLUMN ,1,LEN(SOMECOLUMN ) -1)
WHERE RIGHT(SOMECOLUMN,1) = ';'
Lowell
June 10, 2010 at 9:42 am
You guys are awesome... Thanks both...
June 10, 2010 at 10:19 am
Lowell (6/10/2010)
i think something like this would be better: it's set based, so it's fast, and if the data doesn't start with the offending char, it's not adversly affected:
--remove preceeding semi colon if it exists:
UPDATE SOMETABLE
SET SOMECOLUMN = SUBSTRING(SOMECOLUMN ,2,LEN(SOMECOLUMN ))
WHERE LEFT(SOMECOLUMN,1) = ';'
--remove ending semi colon if it exists:
UPDATE SOMETABLE
SET SOMECOLUMN = SUBSTRING(SOMECOLUMN ,1,LEN(SOMECOLUMN ) -1)
WHERE RIGHT(SOMECOLUMN,1) = ';'
Single update with CASE WHEN is also set-based and it will be faster than two separate updates. To minimise IO you can add a WHERE clause to the update query (don't do it, if it is guaranteed that all your values start or end with ";" so all rows or most of the rows should be updated anyway. The filter will slow down the update as full table scan will be required):
update #MyTable set col1 = case when LEFT(col1, 1) = ';' AND RIGHT(col1, 1) = ';' then substring(col1,2, len(col1) - 2)
when LEFT(col1, 1) = ';' then substring(col1,2,len(col1))
when RIGHT(col1, 1) = ';' then substring(col1,1, len(col1) - 1)
else col1
end
where LEFT(col1, 1) = ';' or RIGHT(col1, 1) = ';'
June 10, 2010 at 2:37 pm
Question that I have is this:
Why in the world are you storing a string value in a single column in a table. You should NEVER, EVER, store string data in a single column. Create the database correctly and use the database as it should be used.
Andrew SQLDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply