[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

replace a string in sql

i want to write an update script where i update just a part of a string .

example


id                           string column

1                              123test
2                              123test1
3                              123test2

i want to replace "123" in string column with "111".  

how do i do that.
0
pratikshahse
Asked:
pratikshahse
  • 2
  • 2
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
Use the STUFF function:

http://technet.microsoft.com/en-us/library/ms188043.aspx

update table
set column = stuff(column,1,3,'111')

This says, replace in column named "column", starting at position 1, for length 3, with the value '111'.
0
 
Simone BSenior E-Commerce AnalystCommented:
You can use the REPLACE function. But I recommend that you first 'eyeball' the data to ensure that you are replacing exactly as you intend to. Also take a backup.

First, double-check:

SELECT YourColumn, REPLACE(YourColumn,'123','111') FROM YourTable

Open in new window


Once you're sure you are replacing as desired, then do the update:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn,'123','111')

Open in new window

0
 
Simone BSenior E-Commerce AnalystCommented:
The STUFF function as described by sjwales will replace the first 3 characters with '111' while the REPLACE function will find the string '123' anywhere and replace it with '111.' So if the value is test123abc, the REPLACE will change it to this:

test111abc

And the STUFF function as described above will change it to this:

111t123abc

It depends on what you're looking for. I may have misread the question.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pratikshahseAuthor Commented:
sjwales,

i have rows where the value is not 123something


id                           string column

1                              123test
2                              123test1
3                              123test2
4                               1254454
5                              145dfadf
6                              123something
0
 
pratikshahseAuthor Commented:
buttercup,

you have read the question right. : )

thanks,
Pratik
0
 
Steve WalesSenior Database AdministratorCommented:
update table
set column = stuff(column,1,3,'111')
where column like '123%'

Or you can use replace as mentioned by Buttercup1, with similar where clause:

where column like '%123%'

if you want to replace 123 anywhere in the string

Stuff does a positional replace.
Replace does an "anywhere in the string" replace.

I chose "stuff" for my reply because that's what your sample data indicated :)

But either will work.

Oh, and here's the doco for REPLACE:

http://technet.microsoft.com/en-us/library/ms186862.aspx
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now