Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question 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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    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)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    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 06: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
  •