September 18, 2008 at 5:45 am
Hi,
New to SQL, can anyone give me any help with an update statement.
Basically need to add 60 to a number, but one that has a letter in it.
For example original may be 543L, new number needs to be 603L.
This needs to apply for anything ending in L.
Any help much appreciated !!
Matt
September 18, 2008 at 5:56 am
Well - roughly, something like this would do it;
UPDATE tblWhatever
SET whateverColumn =
CAST(
60 +
CAST
(
SUBSTRING(WhateverColumn, 1, LEN(WhateverColumn) -1) As INT
)
As VARCHAR(10)
)
+ 'L'
WHERE
RIGHT(WhateverColumn, 1) = 'L'
BUT - if you were to run this, as is, against a large dataset it would most likely perform like a total pig.
If you have concerns in that respect, than some further info as per the links on Jack Corbett's sig would help people provide a better tuned query
September 18, 2008 at 6:09 am
Or better, come to think of it ....
UPDATE tblWhatever
SET whateverColumn =
CASE Surname
WHEN 'Gothard' THEN
CAST(
600 +
CAST
(
SUBSTRING(WhateverColumn, 1, LEN(WhateverColumn) -1) As INT
)
As VARCHAR(10)
)
+ 'L'
ELSE
CAST(
60 +
CAST
(
SUBSTRING(WhateverColumn, 1, LEN(WhateverColumn) -1) As INT
)
As VARCHAR(10)
)
+ 'L'
END
WHERE
RIGHT(WhateverColumn, 1) = 'L'
🙂
September 18, 2008 at 6:13 am
Thanks, for your replies.
The first one worked as expected, however there is a problem in that the taxcode field seems to have spaces after the L. So you might have a field that is '543L ' , and is therefore not being picked up, do you know of something I can add in to combat this ?
Thanks
Matt
September 18, 2008 at 6:15 am
Aye - just bung in an RTRIM()
September 18, 2008 at 6:22 am
Hi Andrew,
Which bit do I put the RTRIM in ?
A bit new to SQL !!
Matt
September 18, 2008 at 7:00 am
SUBSTRING(RTRIM(WhateverColumn), 1, LEN(RTRIM(WhateverColumn)) -1)
AND
WHERE
RIGHT(RTRIM(WhateverColumn), 1) = 'L'
should get you sorted Matt
September 18, 2008 at 7:03 am
Andrew,
Thanks for all your help, managed a little work around by using: Like '%L%', but all good knowledge to be retained for later use !!
Matt
September 18, 2008 at 7:11 am
Glad to have been of assistance
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply