Whats the best way to do the following?

Field1 in Table1 contains numbers and characters seperated by #
Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123

How can I create two new fields in Table1, one containing what is to
the left of the # and the other what is to the right?

Regards,
Ciarán

On 12 May 2005 08:49:49 -0700, chudson007@hotmail.com wrote:

>Whats the best way to do the following?
>
>Field1 in Table1 contains numbers and characters seperated by #
>Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123
>
>How can I create two new fields in Table1, one containing what is to
>the left of the # and the other what is to the right?
>
>Regards,
>Ciarán

Hi Ciarán,

UPDATE MyTable
SET NewCol1 = LEFT(ConcatCol,
CHARINDEX('#', ConcatCol) - 1)
, NewCol2 = SUBSTRING(ConcatCol,
CHARINDEX('#', ConcatCol) + 1,
LEN(ConcatCol))
WHERE ConcatCol LIKE '%#%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Perfect.
Cheers!
Ciarán

Hugo,

How shoud I modify the SQL so that if there is no '#' in ConcatCol,
then NewCol1 = ConcatCol

Regards,
Ciarán

On 13 May 2005 03:07:06 -0700, chudson007@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)