replace 3rd character in a float with case statement.. 3

[Spidy6123]

Hi guys..

we have id numbers.. example: 93785542352

the third digit resembles region.. I need to change the 1's to 5's and 2's to 6's

all in the third digit..

I know access sql has mid() function.. how do I do this efficiently on a SQL server?

TIA

 

[gmmastros]

There's probably a couple ways to do this. Here's one of them. This is written as an 'example' without a table, and a hardcoded variable. Change the value of the variable to see how this works.

declare @Temp Float
Set @Temp = 93785542352

Select Convert(Float, Case When SubString(Convert(VarChar(20), Convert(BigInt, @Temp)), 3, 1) = '1'
            Then Stuff(Convert(VarChar(20), Convert(BigInt, @Temp)),3, 1, '5')
            When SubString(Convert(VarChar(20), Convert(BigInt, @Temp)), 3, 1) = '2'
            Then Stuff(Convert(VarChar(20), Convert(BigInt, @Temp)),3, 1, '6')
            Else Convert(VarChar(20), Convert(BigInt, @Temp))
            End)

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

You can start by CAST/CONVERT ing it to varchar, as string manipulation functions will not work with a float data type.

You can then use the STUFF function to replace the third character (where the third character meets your criteria). Other things that you will need to be familiar with are CASE and SUBSTRING functions.

I suggest you look at everything that I have capitalized in the above paragraphs in books online (aka BOL aka SQL Server Help)

Something like this (assumes you have taken care of the float issue):

[COLOR=green]--set up test data
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (tcol [COLOR=blue]varchar[/color](9))

[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] 123456790
union all [COLOR=blue]select[/color] 12456799
union all [COLOR=blue]select[/color] 12156799
union all [COLOR=blue]select[/color] 12256799

[COLOR=green]--query to show replaced value, original value
[/color][COLOR=blue]select[/color] [COLOR=blue]case[/color]([COLOR=#FF00FF]substring[/color](tcol, 3, 1))
	[COLOR=blue]when[/color] [COLOR=red]'1'[/color] [COLOR=blue]then[/color] 
		[COLOR=#FF00FF]stuff[/color](tcol, 3, 1, [COLOR=red]'5'[/color])
	[COLOR=blue]when[/color] [COLOR=red]'2'[/color] [COLOR=blue]then[/color] 
		[COLOR=#FF00FF]stuff[/color](tcol, 3, 1, [COLOR=red]'6'[/color])
	[COLOR=blue]else[/color] tcol [COLOR=blue]end[/color] [COLOR=blue]as[/color] Changed
, tcol [COLOR=blue]as[/color] Original
[COLOR=blue]from[/color] @t

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

But don't do any of this until you make sure that all related tables will also change (since this is an id filed). You can accomplish this through cascading updates or by updating the tables one ata time. You will have to start with the parent table if that is the one that is changing.

Also do not do this without having an current backup before you start.

"NOTHING is more important in a database than integrity." ESquared

 

[Spidy6123]

lol @ stuff().. interesting naming choice for a function..

I don't know why I didn't see that...
Thanks for all the help guys...