# Thread: Changing Numbers in a String

1. Registered User
Join Date
Oct 2003
Location
London
Posts
341

## Unanswered: Changing Numbers in a String

Hi all,

I'm new to SQL so I'm currently hacking away at bits in long form until I learn the more efficient methods. As a result I don't yet understand how to use all the declare statements etc. which should explain why this is a little long winded...

We have a number that we are using to track employees across the various systems however that number can identify an employee so we are trying to mask the number and make it just that little harder. At the moment I have a select query which uses 10 replace functions to change 1 to A, 2 to B, 3 to C etc... (I'm not really using 1A,2B,3C!)

The concept behind this works however I've just been asked if i could provide the mask in numerical form rather than alphabetic. Unfortunately the replace that I've written doesn't work when turning a number to a different number.

If I say replace 1 with 5, 2 with 6 etc. The 1 becomes a 5, and then in turn is replaced when the replace 5 kicks in etc..

Stage 1) 12345 replace 1 with 5 = 52345
Stage 2) 52345 replace 5 with 9 = 92349

The only way that I can think of getting around this is to say replace all the numbers with letters and then all the letters with numbers but that seems incredibly long.

Can someone help me Turn 12345 into 24680 in such a way that I could reverse the code and get 12345 back if needed?

This is what I was using whilst Alphabetic characters was the solution;

Code:
select emp_no, salutation,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
Right('0000' + cast(emp_no as varchar),8)
,'0','S')
,'1','H')
,'2','B')
,'3','V')
,'4','Y')
,'5','A')
,'6','N')
,'7','E')
,'8','R')
,'9','K') AS upi
from	person
Thank you

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
As an alternative
consider creating a table with employee numbers
add a column for the encoded
then pull the encoded value in place of the employee number as required
that way round your data isn't compromised

3. Registered User
Join Date
Oct 2003
Location
London
Posts
341
Thanks Healdem,

I will consider that, however I'm still stuck with how to convert each number in a string into a new number without affecting one of the previously converted numbers.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
if you want to use your approach then you will have to do a two stage process, convert to say characters, then convert back to the new numbers

5. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
You can always write a function which can do this for you:

Here is an example function:

Code:
DROP FUNCTION IF EXISTS jumbler;
DELIMITER \$\$
CREATE FUNCTION jumbler(pNumber VARCHAR(20))
RETURNS VARCHAR(20)
NOT DETERMINISTIC
SQL SECURITY INVOKER
NO SQL
BEGIN
DECLARE lRes VARCHAR(20) DEFAULT '';
DECLARE lMod VARCHAR(1);
DECLARE lNew INTEGER;
DECLARE lNumber INTEGER;

SET lNumber := pNumber;
WHILE lNumber > 0 DO
SET lMod := RIGHT(lNumber,1);
CASE lMod
WHEN '0' THEN SET lRes := CONCAT(lRes, '4');
WHEN '4' THEN SET lRes := CONCAT(lRes, '0');
WHEN '1' THEN SET lRes := CONCAT(lRes, '8');
WHEN '8' THEN SET lRes := CONCAT(lRes, '1');
WHEN '2' THEN SET lRes := CONCAT(lRes, '3');
WHEN '3' THEN SET lRes := CONCAT(lRes, '2');
WHEN '6' THEN SET lRes := CONCAT(lRes, '9');
WHEN '9' THEN SET lRes := CONCAT(lRes, '6');
WHEN '5' THEN SET lRes := CONCAT(lRes, '7');
WHEN '7' THEN SET lRes := CONCAT(lRes, '5');
END CASE;
SET lNumber := LEFT(lNumber,LENGTH(lNumber)-1);
END WHILE;
RETURN lRes;
END;
\$\$
DELIMITER ;
Here it is in action:

Code:
mysql> select jumbler(12345);
+----------------+
| jumbler(12345) |
+----------------+
| 70238          |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(70238);
+----------------+
| jumbler(70238) |
+----------------+
| 12345          |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(2702382);
+------------------+
| jumbler(2702382) |
+------------------+
| 3123453          |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(3123453);
+------------------+
| jumbler(3123453) |
+------------------+
| 2702382          |
+------------------+
1 row in set, 1 warning (0.00 sec)

6. Registered User
Join Date
Oct 2003
Location
London
Posts
341
Thank you both! Me and a colleague did find a solution in the end although since both of these methods are cleaner we may change it.

The 2 solutions we thought of was a series of substrings looking at 1 character from point 1, then from point 2 etc... cumbersome.

The solution we put into place was a

Code:
;with first_trans as
(
select emp_no, salutation,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
Right('0000' + cast(emp_no as varchar),8)
,'0','S')
,'1','H')
,'2','B')
,'3','V')
,'4','Y')
,'5','A')
,'6','N')
,'7','E')
,'8','R')
,'9','K') AS upi1
from	person
where	date_of_leaving is null
or		date_of_leaving > '2005-12-31 00:00:00.000'
)
select emp_no, salutation, upi1,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(upi1
,'S','4')
,'H','1')
,'B','8')
,'V','7')
,'Y','3')
,'A','0')
,'N','9')
,'E','5')
,'R','6')
,'K','2') AS upi2
from	first_trans
Thank you!
Last edited by christyxo; 10-05-12 at 05:22. Reason: Correction of SQL Statement

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•