Text to number

[rwies]

I have a situation where I have account numbers that contain letters. I need to change the letters to numbers creating an int value. I have several thousand account numbers to change and several different letters to convert.

I would like to convert an "A" to 1, "B" to 2, "C" to 3, etc.

Any ideas as to how to accomplish this easily?

Thanks,

Ron--

 

[JohnDTampaBay]

How does this work for you? I believe ASCII character 65 is A, so 26 iterations should get you all the way through the alphabet. Good luck!

DECLARE @asc tinyint
SET @asc = 1

WHILE @asc <= 26
BEGIN
  UPDATE SomeTable
  SET SomeField = Replace(SomeField, ASC(@asc + 64),
                                Cast(@asc AS varchar(2))

  SET @asc = @asc + 1
END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[rwies]

John,

I pasted the following in Query Analyzer and tried to execute it:

DECLARE @asc tinyint
SET @asc = 1

WHILE @asc <= 26
BEGIN
UPDATE dbo.AccountMainTbl0
SET ID_no = Replace(ID_no, ASC(@asc + 64),
Cast(@asc AS varchar(2))

SET @asc = @asc + 1
END

I got the following message:

Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ASC'.

Any ideas?

Ron--

 

[earthandfire]

In SQL Server the ASC function is ASCII


Hope this helps.

 

[JohnDTampaBay]

Brain cramp. My apologies.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[rwies]

Earthandfire & John,

I changed the code to:

DECLARE @asc tinyint
SET @asc = 1

WHILE @asc <= 26
BEGIN
UPDATE dbo.AccountMainTbl0
SET ID_no = Replace(ID_no, ASCii(@asc + 64),
Cast(@asc AS varchar(2))

SET @asc = @asc + 1
END

I then got the following error:

Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'SET'.


Set is used several times but I assume this refers to the SET in SEt Id_no =

I'm not entirely sure what is supposed to happen so I don't know what to change.

Thanks for our assistance.

Ron--

 

[JohnDTampaBay]

I think a parentheses was forgotten somewhere. Darn those brain cramps.

What we are attempting to do is make 26 iterations through the BEGIN...END block, one for each letter of the alphabet. We are using the REPLACE function to swap the letter with the number. We are using the ASCII function to convert a numeric value to its ASCII character equivalent. Since ASCII 65=A, 66=B, etc. then @asc + 64 should resolve to each character of the alphabet as we step through the loop.

DECLARE @asc tinyint
SELECT @asc = 1

WHILE @asc <= 26
BEGIN
  UPDATE dbo.AccountMainTbl0
  SET ID_no = Replace(ID_no, ASCii(@asc + 64),
                                Cast(@asc AS varchar(2) ) )

  SELECT @asc = @asc + 1
END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mtnds]

I think you need to parse your ID_no and only replace the relevant characters if I understand the situation. (i.e. ID_no 12A3 would become 1213, and 12Z3 would become 12263.)

Take a look at the BOL entry for ASCII.

--Michael

 

[rwies]

John,

I added the parentheses and the code ran without error.

However, I don't see any change to the text. Michael is right, the code I am trying to chnge is similar to 1A234. I want it to be 11234 after running the code. There may be more than one letter in the text such as 1ca234. In this case I want the final version to be 131234.

Thanks,

Ron--

 

[JohnDTampaBay]

Right road, wrong car. ASCII function converts character to number, CHAR is what we want.

DECLARE @asc tinyint
SELECT @asc = 1

WHILE @asc <= 26
BEGIN
  UPDATE dbo.AccountMainTbl0
  SET ID_no = Replace(ID_no, [b]CHAR[/b](@asc + 64),
                                Cast(@asc AS varchar(2) ) )

  SELECT @asc = @asc + 1
END

I tested it this time and it worked. What a lughead. That will teach me to code during the basketball game.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JohnDTampaBay]

Also, mtnds makes an important point. If you have ID numbers 12Z3 and 12BF3, both will convert to the same value: 12263. Be very careful!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)