CODEWITHSUNDEEP
sqlsql-servert-sql
Tim Vavra
Tim Vavra

Reputation: 537

Convert strings in multiple formats to a single, standard format

I update a table using a stored procedure that imports information from SSRS. I have a field that is called page. The page field can contain one of three formats:

  1. A purely numeric field eg 1,2 3 etc.
  2. A number and letter combination eg 1D, 4C, 7Z etc.
  3. A number, a dash and two additional numbers eg 2-01, 3-04, 1-09 etc

I need to create a page equivalence for each of the formats for sorting purposes as well as to help define a general category of page numbers. Example 1 is a Base Page, example 2 is an Art Page and example 3 is a Text Page.

For the equivalence, example 1 would result in a 1.00 format. Example 2 would result in a 1.01 through 1.26 equivalence based on the numeric equivalent of the letter. Finally, example 3 would result in 1.26 plus .01 * the value to the right of the dash. Minimum value is 1.27.

I have done this in Excel but cannot seem to write the formula correctly in SQL.

I am using SQL Server 2008 R2

Upvotes: 2

Views: 155

Answers (3)

Andriy M
Andriy M

Reputation: 77687

Here's another option.

Although this answer is based off Pondlife's, it returns decimal results instead of strings.

It heavily employs implicit conversion, which is why it may look more compact.

It also assumes that only the three formats mentioned in your question are used. If there's a value with an "invalid" format, it will break this solution.

SELECT
  CASE
    WHEN PageNo NOT LIKE '%[^0-9]%' THEN PageNo
    WHEN PageNo     LIKE '%[A-Z]'   THEN LEFT(PageNo, LEN(PageNo) - 1)
                                         + (ASCII(RIGHT(PageNo, 1)) - 64) * 0.01
    WHEN PageNo     LIKE '%-%'      THEN REPLACE(PageNo, '-', '.') * 1.0 + 0.26
  END
FROM ...

Upvotes: 1

Pondlife
Pondlife

Reputation: 16240

This seems to cover the cases you describe:

declare @t table (val varchar(10))
insert into @t 
select '1' union select '2' union
select '1D' union select '4S' union
select '2-01' union select '1-09'

select val, case
    when val not like '%[^0-9]%' then val + '.00'
    when val like '[0-9][A-Z]' then '1.' + right('0' + convert(varchar, ascii(right(val, 1))-64), 2)
    when val like '%-%' then convert(varchar, 1.26 + (0.01 * cast(substring(val, charindex('-', val)+1, 2) as int)))
    else val end
from
    @t

TSQL is not a great language for string manipulation and parsing, so you may want to consider using a CLR function or stored procedure for this. And applying these rules is a good candidate for unit testing, because it's simple to define and use a test set of input and output values to verify your code.

Upvotes: 1

Mike Clark
Mike Clark

Reputation: 11979

This is ugly and probably not very performant, but:

select
  page_num,
  case
    when page_num like '%A' then substring(page_num, 1, len(page_num)-1) + '.01'
    when page_num like '%B' then substring(page_num, 1, len(page_num)-1) + '.02'
    when page_num like '%C' then substring(page_num, 1, len(page_num)-1) + '.03'
    when page_num like '%D' then substring(page_num, 1, len(page_num)-1) + '.04'
    when page_num like '%E' then substring(page_num, 1, len(page_num)-1) + '.05'
    when page_num like '%F' then substring(page_num, 1, len(page_num)-1) + '.06'
    when page_num like '%G' then substring(page_num, 1, len(page_num)-1) + '.07'
    when page_num like '%H' then substring(page_num, 1, len(page_num)-1) + '.08'
    when page_num like '%I' then substring(page_num, 1, len(page_num)-1) + '.09'
    when page_num like '%J' then substring(page_num, 1, len(page_num)-1) + '.10'
    when page_num like '%K' then substring(page_num, 1, len(page_num)-1) + '.11'
    when page_num like '%L' then substring(page_num, 1, len(page_num)-1) + '.12'
    when page_num like '%M' then substring(page_num, 1, len(page_num)-1) + '.13'
    when page_num like '%N' then substring(page_num, 1, len(page_num)-1) + '.14'
    when page_num like '%O' then substring(page_num, 1, len(page_num)-1) + '.15'
    when page_num like '%P' then substring(page_num, 1, len(page_num)-1) + '.16'
    when page_num like '%Q' then substring(page_num, 1, len(page_num)-1) + '.17'
    when page_num like '%R' then substring(page_num, 1, len(page_num)-1) + '.18'
    when page_num like '%S' then substring(page_num, 1, len(page_num)-1) + '.19'
    when page_num like '%T' then substring(page_num, 1, len(page_num)-1) + '.20'
    when page_num like '%U' then substring(page_num, 1, len(page_num)-1) + '.21'
    when page_num like '%V' then substring(page_num, 1, len(page_num)-1) + '.22'
    when page_num like '%W' then substring(page_num, 1, len(page_num)-1) + '.23'
    when page_num like '%X' then substring(page_num, 1, len(page_num)-1) + '.24'
    when page_num like '%Y' then substring(page_num, 1, len(page_num)-1) + '.25'
    when page_num like '%Z' then substring(page_num, 1, len(page_num)-1) + '.26'
    when page_num like '%-%' then 
      substring(page_num, 1, charindex('-', page_num)-1) + '.' + 
      cast((26+cast(substring(page_num, charindex('-', page_num)+1, len(page_num)) as int)) as varchar(12))                                                                                                          
    else page_num + '.00'
  end
from pages

See SQL Fiddle for full schema I was testing out.

That said, I would strongly recommend updating your schema to something that includes the normalized page numbers and convert this on the way into the table.

Upvotes: 0

Related Questions