Solved

replace a string in sql

Posted on 2014-01-13
6
308 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

840 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