Update Query

[gfranklin]

I'm in desperate need of some help with an SQL Update
query...

I have a column that contains serial numbers and a fail
code divided by a dash. I would like to split the columns
into two separate columns but, the problem is they aren't
the same length.

example: 980890121-1, 239451-1, 000509197Z-1, 4333260-1

what would the correct syntax be for my statement?
I know I want to start at one but what is the length going to be? It could be different on all of them?

Update CallLog
set SerialNumber = substring(CLSerialNumber, 1, ?

this is where I'm stuck! Please help!

Please help! Thanks Gabby

 

[fluteplr]

look at charindex.

 

[Sqlsa]

Hi,
Try this..

Update CallLog
set SerialNumber=
left(CLSerialNumber,charindex('-',CLSerialNumber))

Refer BOL for Functions List..You will find a lot about String functions, Date functions, all kinds of functions..

Better look at BOL.

Hope it helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[gfranklin]

Sreenivas

That worked great! I have just one more question.
Now how can I delete the - from the end of the serial number?

Thanks, Gabby

 

[Sqlsa]

Hi Gabby,

(1) Use REPLACE function to take care of '-' in SERIALNUMBER.

-----------------------------------------
--To take care of '-' in SerialNumber

Update CallLog
set SerialNumber=
REPLACE(SerialNumber,'-','')
-----------------------------------------


-----------------------------------------
(2) But the actual UPDATE should have been :
-To take care of all '-' in SerialNumber use the following:
Update CallLog
set SerialNumber=
left(CLSerialNumber,charindex('-',CLSerialNumber)-1)
where charindex('-',CLSerialNumber)>=1
---------------------------------
Observe the change. After charindex is performed, we are subtracting 1 from it. So you won't get '-' character at all.


So, if you update it next time, use the following UPDATE:
-------------------------------------------------
Update CallLog
set SerialNumber=
left(CLSerialNumber,charindex('-',CLSerialNumber)-1)
where charindex('-',CLSerialNumber)>=1
------------------------------------------------------

Here a "where clause" is added so that, it will update only for those CLSerialNumbers which have a '-' in it. If CLSerialNumber does not have a '-' charcter, it won't update that record as it is not necessary. If this WHERE clause is not used , you may get error for those records which don't have a '-' in CLSerialNumber.

I think, that should take care of it.

Hope it helps.

Sreenivas
avnsr@hotmail.com
-----------------