Solved

replace a string in sql

Posted on 2014-01-13
6
302 Views
Last Modified: 2014-01-13
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
Comment
Question by:pratikshahse
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39777590
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
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 39777598
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
 
LVL 11

Expert Comment

by:Simone B
ID: 39777603
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pratikshahse
ID: 39777605
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
 

Author Comment

by:pratikshahse
ID: 39777606
buttercup,

you have read the question right. : )

thanks,
Pratik
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39777607
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now