Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Replace one character within a field

Author  Topic 

EMMERSON
Starting Member

3 Posts

Posted - 2013-03-07 : 06:24:58
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-07 : 06:35:38
SELECT Replace(ColumnName,'x','X') FROM YourTableName;

Cheers
MIK
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-07 : 06:36:40
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

52326 Posts

Posted - 2013-03-07 : 06:45:47
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/

Go to Top of Page

EMMERSON
Starting Member

3 Posts

Posted - 2013-03-07 : 07:12:16
Thanks that should do the trick
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 08:43:41
welcome

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

Go to Top of Page
   

- Advertisement -