Loop through an integer and test the values and update with a char

Question

Post reply

Loop through an integer and test the values and update with a char

leninepestana

SSC Veteran

Points: 205
More actions
August 17, 2016 at 10:57 am

#400589

Say I have a table Products with a field price and a field PriceInChar:
price
12345678.90
priceInChar
I want to select the values from products but the price must match this format equivalent and
must update the field PriceInChar with the equivalent value in char:
0->A
1->B
2->C
3->D
4->E
5->F
6->G
7->H
8->I
9->J
THE CORRESPONDING FIELD updated in priceInChar
price
12345678.90
priceInChar
BCDEFGHI.JA
Thank you

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

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

ScottPletcher SSC Guru Points: 101129 More actions · Answer 1

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

CAST(price AS varchar(20)),

'0', 'A'),

'1', 'B'),

'2', 'C'),

--...

'8', 'I'),

'9', 'J') AS priceInChar

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

A couple other ways:

1. Use NGrams8K[/url] like so:

DECLARE @price decimal(12,2) = 12345678.90;

SELECT NewPrice =

(

SELECT CASE WHEN token LIKE '[0-9]' THEN CHAR(ASCII(token)+17) ELSE token END

FROM dbo.NGrams8k(@price, 1)

FOR XML PATH('')

);

2. Use Translate8K[/url] like so:

SELECT dbo.Translate8K(@price, '0123456789', 'ABCDEFGHIJ');

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Just an opinion... you just about can't beat a Nested Replace, like what Scott wrote, for something like this. I say "just about" only because I don't know of something faster but I suppose there could be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Jeff Moden (8/17/2016)
Just an opinion... you just about can't beat a Nested Replace, like what Scott wrote, for something like this. I say "just about" only because I don't know of something faster but I suppose there could be.

+1

I intended to mention that but carried away 😛

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

Alan.B (8/17/2016)
Jeff Moden (8/17/2016)
Just an opinion... you just about can't beat a Nested Replace, like what Scott wrote, for something like this. I say "just about" only because I don't know of something faster but I suppose there could be.
+1
I intended to mention that but carried away 😛

The NGRAMS tool you wrote is very cool. Don't blame you a bit. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kramaswamy SSCoach Points: 18181 More actions · Answer 6

if you don't mind me asking, I really do wonder, what possible reason could you have for wanting to recode your numbers as letters o_O? Some sort of archaic way of hiding prices in plain sight?

leninepestana SSC Veteran Points: 205 More actions · Answer 7

Thank you very much, it works with some arrangements, but it works perfectly.

leninepestana SSC Veteran Points: 205 More actions · Answer 8

leninepestana

SSC Veteran

Points: 205

August 18, 2016 at 4:58 am

#1896106

Yes it's true:-)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 9

The NGRAMS tool you wrote is very cool. Don't blame you a bit. 🙂

😀

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001