Replacing first letter only in column

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

Replacing first letter only in column

Author

Topic

Randy A
Starting Member

2 Posts

Posted - 2006-05-17 : 15:03:00

Hello. We have a list of 1000+ sku numbers. 90% of them start with u. I need to replace the u with T.

Concerns:
It has to only replace the first letter, if there are other "u"s in the sku, they need to remain.
Not all skus start with u, those that don't should remain the same.

I have searched and searched because I'm sure someone has requested the exact same thing but I can't find anything that is identical.

I have this so far but I know its not correct:
set sku = replace(left(sku,1),'u',right(rtrim(sku),1)+
'T')

Can someone help lead me down the right path?

Thanks in advance!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-17 : 15:07:25

SET REPLACE(SUBSTRING(sku, 1, 1), 'u', 'T') + SUBSTRING(sku, 2, DATALENGTH(sku))

or

SET REPLACE(LEFT(sku, 1), 'u', 'T') + SUBSTRING(sku, 2, DATALENGTH(@sku))

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-17 : 15:13:35

Declare @t table(sku varchar(10))
Insert into @t values('t12345')
Insert into @t values('u33333')
Insert into @t values('Z22222')
Insert into @t values('Puuuuu')

Update @t set sku = 't' + Right(sku,datalength(sku)-1) where left(sku,1) = 'u'
Select * from @t

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-17 : 15:22:27

quote:

Update @t set sku = 't' + Right(sku,datalength(sku)-1) where left(sku,1) = 'u'

This will be a little slower than the solutions that I posted due to the WHERE clause, especially considering that the column name is not isolated to either side. My solutions do not require a WHERE clause. The REPLACE functions find the u and replaces it with a T, so it doesn't touch any other data.

Tara Kizer
aka tduggan

Randy A
Starting Member

2 Posts

Posted - 2006-05-17 : 16:52:54

Thanks everyone, this is what I used based on your responses above and it worked GREAT:

UPDATE [db].[dbo].[products]
SET SKU = REPLACE(SUBSTRING(sku, 1, 1), 'u', 'T') + SUBSTRING(sku, 2, DATALENGTH(sku))

I very much appreciate you all taking time to respond!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources