What is the best way to update a single character in a 20 byte field?

[davcomp]

I need to change a single character in a 20 byte field. I tried using the SUBSTRING operation but when I run it I lose everything in the field. I don't think I have the syntax right. My table name is SECGROUP.

group_id x(10)
group_actions x(20)
exp_days x(03)

I need to change the tenth position in the field group_actions from a '1' to a '0'.

Here is my sql:

exec sql update =SECGROUP
set group_actions = substring('0', from 10 for 1)
where group_id = 'JCPENY'

what am I doing wrong?

 

[clapag22]

update secgroup
set group_actions = left(group_actions, 9) + '0' + right(group_actions, 10)
where groupid = 'JCPENY'

 

[tlbroadbent]

You can use the Stuff function.

Update table_name
Set group_actions = stuff(group_actions, 10, 1, '0')
where group_id = 'JCPENY'
and substring(group_actions, 10, 1) = '1'

Or you can use the Replace function.

Update table_name
Set group_actions = replace(group_actions, '1', '0')
where group_id = 'JCPENY'
and substring(group_actions, 10, 1) = '1' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[clapag22]

Be aware:

The REPLACE function will replace ALL occurrences of '1' with '0' within the string.

 

[tlbroadbent]

clapag22,

Thanks for the correction. I obviously wasn't thinking clearly when I made that post. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[davcomp]

Thanks for everybody's help!! Both the (left & right) and (stuff) functions worked.