Replace one character within a field

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

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

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/

EMMERSON
Starting Member

3 Posts

Posted - 2013-03-07 : 07:12:16

Thanks that should do the trick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources