How to replace first 2 characters in a column

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

Hi Guys ,

How to replace first 2 characters in a column

examplle :

28850223169
28850256169
28770223169
28602231698
28950111169

Here first 2 number should replace with 39 it wold be

39850223169
39850256169
39770223169
39602231698
39950111169

How to replace like that please tell me how to do

i have tried like this but

SQL

UPDATE number
SET num = replace(num, '28', '39')

its replacing 39 where ever 28 present but i need to change only first 2 numbers to 39

Please tell me guys how to do
thanks and regards
mani

Posted 18-Jun-15 20:39pm

u mani

Add a Solution

Comments

Thanks7872 19-Jun-15 2:57am

Did you try any of this solutions?

https://www.google.co.in/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=sql%20replace%20first%20n%20characters

3 solutions

Solution 2

hi,

Try this one too.

SQL

UPDATE number
SET    num= Replace(num, Substring(num, 1, 2), 39)

Posted 18-Jun-15 22:44pm

Dhamodharan A

Updated 18-Jun-15 22:47pm

v2

Comments

jaket-cp 19-Jun-15 5:15am

The op only wanted the first two numbers to be replaced.
Not all instances of the first two numbers.

Dhamodharan A 19-Jun-15 5:53am

SO, As per my understanding u need to replace the first two occurrence of 28 to 39 , if suppose 10th row having 55 as starting number u no need to replace ,only the 28 to 39 ..? Am i right ???

jaket-cp 19-Jun-15 5:57am

Hello I am not OP.
What I was stating is, the replace will replace all instances of Substring(num, 1, 2).
Where the OP only requires the first 2 numbers to be replaced.

Dhamodharan A 19-Jun-15 6:47am

use this one will work

UPDATE number
SET num= '39'+''+right(num, Len(num)-2)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dhamodharan A · Accepted Answer · 2015-06-19T00:49:00

Solution 3

its match your requirement :

SQL

UPDATE number
SET    num= '39'+''+right(num, Len(num)-2)

Thanks
Dhamu

Posted 19-Jun-15 0:49am

Dhamodharan A

Tomas Takac · Accepted Answer · 2015-06-18T20:51:00

Solution 1

The best way would be to trim the first two characters using substring and then prepend it with '39':

SQL

UPDATE number
SET num = '39'+substring(num, 3, len(num))

Posted 18-Jun-15 20:51pm

Tomas Takac

Updated 18-Jun-15 21:22pm

v2

Comments

u mani 19-Jun-15 3:03am

thanks for replay.. how to update in the table ?

Tomas Takac 19-Jun-15 3:22am

You already have the update statement, correct? Your question was about the text transformation. Nevertheless see my updated solution.

u mani 19-Jun-15 3:34am

yes thank u very much

jaket-cp 19-Jun-15 6:17am

Just a small point, if you want only numbers starting with 28 to be replaced, you will need a where clause to be put in place.
Something like this: where substring(num, 1, 2) = '28';