SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Replace one character within a field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EMMERSON
Starting Member

2 Posts

Posted - 03/07/2013 :  06:24:58  Show Profile  Reply with Quote
Hi

Im relatively new to SQL.

How could i replace one character within a field but leave the rest how they are.

Example

I have a field which consists of x followed by 5 numbers

x12345
x23456
x55555.

I need to replace the x with a capital X

Thanks

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2013 :  06:35:38  Show Profile  Reply with Quote
SELECT Replace(ColumnName,'x','X') FROM YourTableName;

Cheers
MIK
Go to Top of Page

rjhe22
Posting Yak Master

248 Posts

Posted - 03/07/2013 :  06:36:40  Show Profile  Reply with Quote
could do an update on the field with the new data
update table1
set col1 = X12345
where col1 = x12345

something like that
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  06:45:47  Show Profile  Reply with Quote
First suggestion will work so far as you've format consistent in your field. if you happen to have other x characters inside they'll also get replaced.

so better approach might be below if your format is not consistent.

update table1
set col1='X' + STUFF(col1,1,1,'')
WHERE col1 LIKE 'x%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 03/07/2013 06:46:21
Go to Top of Page

EMMERSON
Starting Member

2 Posts

Posted - 03/07/2013 :  07:12:16  Show Profile  Reply with Quote
Thanks that should do the trick
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  08:43:41  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000