Updating first character 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

SQL Server 2000 Forums

Transact-SQL (2000)

Updating first character in column

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-09-05 : 10:53:25

Hey i'd like to update the first character of my cust_code

they are currently like this 'Z12345' 'Z54321'

l'd like them to be updated to 'B12345' 'B54321'

Am trying to do it like this but my sql statement is not correct.

update customers
set substring(cust_code,1,1) = 'B'
from customers
where substring(cust_code,1,1) = 'Z'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 11:05:29

Use this logic

declare @v varchar(100)
set @v='Z12345''Z54321'
select 'B'+substring(@v,2,charindex('''',@v)-1)+'''B'+substring(@v,charindex('''',@v)+2,len(@v))

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-09-05 : 11:16:09

I've about 500 customer to update so i can't hard code the cust_code

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-05 : 13:21:39

[code]DECLARE @Customers TABLE (cust_code VARCHAR(20))

INSERT @Customers
SELECT 'Z12345'
UNION ALL SELECT 'Z54321'

UPDATE
@Customers
SET
cust_code = STUFF('B', 1, 1, cust_code)
WHERE
cust_code LIKE 'Z%'[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-06 : 02:25:18

quote:
Originally posted by rookie_sql

I've about 500 customer to update so i can't hard code the cust_code

I misunderstood your question. Try this

update customers
set cust_code='B'+substring(cust_code,2,len(cust_code))
from customers
where cust_code like 'Z%'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-06 : 02:26:25

quote:

Originally posted by Lamprey

DECLARE @Customers TABLE (cust_code VARCHAR(20))

INSERT @Customers
SELECT 'Z12345'
UNION ALL SELECT 'Z54321'

UPDATE 
	@Customers
SET 
	cust_code = STUFF('B', 1, 1, cust_code)
WHERE 
	cust_code LIKE 'Z%'

That should be

UPDATE
@Customers
SET
cust_code = STUFF(cust_code,1, 1,'B' )
WHERE
cust_code LIKE 'Z%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources