How to update particular digit in a field

Question

How to update particular digit in a field

neillewis

SSC Veteran

Points: 226
More actions
June 9, 2011 at 6:13 am

#389694

Hi,
I have a column (DataType is Decimal(19,0)) for which I need to update just one digit.
As an example, say I need to change
1111111111111111111
To
1111111111191111111
How do I do that?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

Is changing the 12th character always the one you want changed or does it vary?

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

neillewis SSC Veteran Points: 226 More actions · Answer 2

It's always the 12th for this update and it is updating it to the same number

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

neillewis (6/9/2011)
It's always the 12th for this update and it is updating it to the same number

That is a strange request but here you go.

create table #num

( val decimal(19,0))

insert #num select 1111111111111111111

select * from #num

declare @NewVal char(1) = '9'

update #num set val = LEFT(cast(val as varchar(20)), 11) + @NewVal + RIGHT(cast(val as varchar(20)), datalength(cast(val as varchar(20))) - 12)

select * from #num

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

calvo SSChampion Points: 12930 More actions · Answer 4

Good solution Mr. Lange.

I was using stuff and replace functions to get it done, but I wasn't happy with it. Then a tally table solution ran through my mind.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

neillewis SSC Veteran Points: 226 More actions · Answer 5

Thank you Sean!

That is exactly what I was after. Just one tiny thing I had to change the Declare @NewVal to set the value separately i.e.

declare @NewVal char(1)

SET @NewVal = '9'

Instead of

declare @NewVal char(1) = '9'

I got the error: Cannot assign a default value to a local variable.

After that, worked like a charm.

A strange request yes, I get them every day 😉 The 1111111111111111111 is just a dummy number though, the real values are a composite of loads of other numbers to create a unique identifier, some data came through with the wrong number in the middle which I had to change. This did the trick!

Thanks again

Neil

neillewis SSC Veteran Points: 226 More actions · Answer 6

calvo (6/9/2011)
Good solution Mr. Lange.
I was using stuff and replace functions to get it done, but I wasn't happy with it. Then a tally table solution ran through my mind.

Yes I was trying to use the replace function too, but for this I think you need a unique string to replace which wasn't possible in this case....

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

neillewis (6/9/2011)
Thank you Sean!
That is exactly what I was after. Just one tiny thing I had to change the Declare @NewVal to set the value separately i.e.
declare @NewVal char(1)
SET @NewVal = '9'
Instead of
declare @NewVal char(1) = '9'
I got the error: Cannot assign a default value to a local variable.
After that, worked like a charm.
A strange request yes, I get them every day 😉 The 1111111111111111111 is just a dummy number though, the real values are a composite of loads of other numbers to create a unique identifier, some data came through with the wrong number in the middle which I had to change. This did the trick!
Thanks again
Neil

You must be on 2005 or earlier then (or at least compatibility mode)? Nice feature introduced in 2008 to assign a default inline like that.

I assumed the data was not real values but an example. Glad the solution worked for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

neillewis SSC Veteran Points: 226 More actions · Answer 8

Correct again Sean, SQL 2000 🙁

Big project to upgrade to 2008 at the moment!