CODEWITHSUNDEEP
sqlsql-server
Gordon Copestake
Gordon Copestake

Reputation: 1637

SQL String Manipulation and character replacement

I have a database with a list of old ItemID's that need updating to a new format.

The old format is of the form 8046Y and the new format moves the 4th digit to the end and prepends a hyphen and adds a 0 if it's a single digit. The old format also uses alpha characters when the number goes over 9 for example 464HB where the H represents 17. I also need to add a 1 to the beginning of the new format. All this can be done with string manipulation in SQL I hope.

Some examples:

8046Y becomes 1804Y-06
464HB becomes 1464B-17 (H = 17)

Can anyone give me some pointers as to how to go about this in SQL?

I got as far as:

select '1' + LEFT(ItemID, 3) + RIGHT(ItemID,1) + '-' + '0' + SUBSTRING(ItemID,3,1) from items

But the conversion from a=10 to z=36 stumped me

Upvotes: 0

Views: 134

Answers (5)

zbads
zbads

Reputation: 135

Personally, I'd create a function for it.

  1. Create a variable to handle the new value.
  2. Manipulate the positions through SUBSTRING, you can also use RIGHT or LEFT
  3. When adding zeros in single digit numbers, just do conditional statement
  4. Regarding the conversion of letters to numbers(e.g. letter H), the converted value of the first letter is 65 assuming it's all capital. So, A=65, B=66, H=72 and so on. Use this data in manipulating the values. Uhm, I'll give you my thoughts but you can optimize it (since I don't have lots of time).

Taking your example of H=17, so A=10. Just subtract 55 in the conversions. So H=72(-55) becomes 17. This is applicable to all letters (in uppercase only).

I think this much is more than enough to guide you. Hope this would help.

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

Try this:

select 
'1' 
+ left(@str,3) 
+ right(@str,1) 
+ '-' 
+ case 
      when substring(@str,4,1) like '%[0-9]%' 1 then right('00' + substring(@str,4,1),2)
      else cast(10 + ascii(substring(@str,4,1))-ascii('A') as varchar(2)) 
  end

Explanation: If the 4th character is a digit, then do not change the value and append it after padding with a zero. Otherwise, use ASCII to get the ASCII value for that character, get it's difference with the value for 'A' and add the offset of 10.

Demo

Upvotes: 1

deterministicFail
deterministicFail

Reputation: 1285

I do it only for the challenge, i dont recommend to use 

DECLARE @id varchar(5) = '8046Y'
--SET @id = '464HB'

SELECT 
    '1' + 
    LEFT(@id, 3) + 
    RIGHT(@id, 1) + 
    '-' + 
    CASE WHEN ISNUMERIC(RIGHT(LEFT(@id, 4), 1)) = 1 THEN
            RIGHT(LEFT(@id, 4), 1)
         ELSE
            RIGHT('00' + CONVERT(VARCHAR, ASCII(RIGHT(LEFT(@id, 4), 1)) - 64 + 9), 2)
    END

-64 for the Start of ASCII A and +9 for your convention

Upvotes: 1

Daniel E.
Daniel E.

Reputation: 2059

select '1' + LEFT(ItemID, 3) + RIGHT(ItemID,1) + '-'
    +CASE RIGHT(LEFT(ItemID,1),2)
        WHEN 'a' then 10
        WHEN 'b' THEN 11
        etc...
    END [NewItemID]
 from items

Just add the appropriate cases in that format.

Upvotes: 1

Lmu92
Lmu92

Reputation: 952

Since I don't know if there are any other special character to consider except 'H' only included this single character.

DECLARE @val CHAR(5) = '464HB'
SELECT @val, '1' + LEFT(@val,3)+SUBSTRING(@val,5,1)+'-'+CASE WHEN +SUBSTRING(@val,4,1)='H' THEN '17' ELSE '0'+SUBSTRING(@val,4,1) END

Upvotes: 1

Related Questions