On 13 May 2005 03:07:06 -0700,
ch********@hotmail.com wrote:
Hugo,
How shoud I modify the SQL so that if there is no '#' in ConcatCol,
then NewCol1 = ConcatCol
Regards,
Ciarán
Hi Ciarán,
UPDATE MyTable
SET NewCol1 = CASE
WHEN ConcatCol LIKE '%#%'
THEN LEFT(ConcatCol,
CHARINDEX('#', ConcatCol) - 1)
ELSE ConcatCol
END
, NewCol2 = CASE
WHEN ConcatCol LIKE '%#%'
THEN SUBSTRING(ConcatCol,
CHARINDEX('#', ConcatCol) + 1,
LEN(ConcatCol))
ELSE NULL
END
Or you could simply run two consecutive UPDATE statements (the one from
my first message, followed by an update that sets newcol1 equal to
concatcol for the remaining rows).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)