Microsoft SQL Server forum - Reversing string concatenation

Author

Reversing string concatenation

Shwetabh

2006-04-05, 3:28 am

Hi,

How can I remove a part of string from complete string in SQL?

this is something i want to do:

update ace
set PICTURE = PICTURE - '\\SHWETABH\Shwetabh
\I\'

But here ' - ' is not allowed.

How can I remove the string \\SHWETABH\Shwetabh\
I\ from the column
PICTURE?

Regards,
Shwetabh

m.bohse@quest-consultants.com

2006-04-05, 3:28 am

Try this
UPDATE ace
SET picture = Replace(Picture,'\\S
HWETABH\Shwetabh\I\'
, '')

Markus

Madhivanan

2006-04-05, 9:30 am

Also try

UPDATE ace
SET picture = Substring(Picture,1,
charindex('-',Picture,1)-1)

Madhivanan

das

2006-04-05, 1:37 pm

Or if you know that the first part of the string is always going to be
'PICTURE' then simply use this:

update ace
set PICTURE = left(PICTURE, 7)

Shwetabh

2006-04-06, 7:33 am

Thanks Marcus,
That did the trick.

Now there is one more problem.
I know to add the string, the query is

UPDATE ace
SET PICTURE = '\\SHWETABH\Shwetabh
\I\' + PICTURE

Now this query appends the string before PICTURE column contents.
Now is there a way that the contents having the string
'\\SHWETABH\Shwetabh
\I\'
do not get updated with this string and only cells which do not have
the string get updated?

Shwetabh

Hugo Kornelis

2006-04-06, 8:26 pm

On 6 Apr 2006 02:32:02 -0700, Shwetabh wrote:

>Thanks Marcus,
>That did the trick.
>
>Now there is one more problem.
>I know to add the string, the query is
>
>UPDATE ace
>SET PICTURE = '\\SHWETABH\Shwetabh
\I' + PICTURE
>
>Now this query appends the string before PICTURE column contents.
>Now is there a way that the contents having the string
> '\\SHWETABH\Shwetabh
\I'
>do not get updated with this string and only cells which do not have
>the string get updated?

Hi Shwetabh,

UPDATE ace
SET PICTURE = '\\SHWETABH\Shwetabh
\I' + PICTURE
WHERE PICTURE NOT LIKE '\\SHWETABH\Shwetabh
\I\%'

--
Hugo Kornelis, SQL Server MVP

Sponsored Links