• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

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.

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