LeSteelBox LeSteelBox - 7 months ago 11
SQL Question

Parsing inconsistent data with SQL

I need to write SQL to extract repeat location codes and separate out the sub-location detail. However, the data I am working with does not follow a set pattern.

Here's a sample of what the location codes look like (the real table has over 5,000 locations):

JR-DY-TIN
DY-RHOLD
DY-PREQ-TIN
GLVCSH
GLFLR
GLBOX1
GLBOX2
GLBOX3
GLBOXA
GLBOXB
GLBOXC
GLBOXD
GL
GL0001
GL0002
GL0003
GL0014



I was able to create a new column for the sub-location detail when it is numeric but that's all I have so far.

select
LocationCode,
REVERSE(LEFT(REVERSE(LocationCode),PATINDEX('%[A-Za-z]%',
REVERSE(LocationCode))-1)) AS PaddedNumbers
from LocationTable


Results...

LocationCode PaddedNumbers
------------ -------------
JR-DY-TIN
DY-RHOLD
DY-PREQ-TIN
GLVCSH
GLFLR
GLBOX1 1
GLBOX2 2
GLBOX3 3
GLBOXA
GLBOXB
GLBOXC
GLBOXD
GL
GL0001 0001
GL0002 0002
GL0003 0003
GL0014 0014


I still figure out how to display the following in two separate columns:


  • Location codes without the sub-locations detail, e.g. GLBOX , or just
    the original location code if there is no sub-location, e.g. GLFLR.

  • Numeric and Nonnumeric sub-location detail at the same time, e.g. for
    GLBOX have a column that displays 1, 2, 3,A, B, C, D, E, F.



Edit: If I am able to accomplish this the data should look like this:

LocationCode MainLoc SubLoc
------------ --------- ------
JR-DY-TIN JR-DY-TIN
DY-RHOLD DY-RHOLD
DY-PREQ-TIN DY-PREQ-TIN
GLVCSH GLVCSH
GLFLR GLFLR
GLBOX1 GLBOX 1
GLBOX2 GLBOX 2
GLBOX3 GLBOX 3
GLBOXA GLBOX A
GLBOXB GLBOX B
GLBOXC GLBOX C
GLBOXD GLBOX D
GL GL
GL0001 GL 0001
GL0002 GL 0002
GL0003 GL 0003
GL0014 GL 0014


Any help is appreciated.

Environment: SQL Server 2008 R2.

Answer
DECLARE @LocationRef TABLE (Location NVARCHAR(20), Ref INT)

INSERT INTO @LocationRef VALUES
     ('JR-DY-TIN',0)
    ,('DY-RHOLD',0)
    ,('DY-PREQ-TIN',0)
    ,('GLVCSH',0)
    ,('GLFLR',0)
    ,('GLBOX1',6)
    ,('GLBOX2',6)
    ,('GLBOX3',6)
    ,('GLBOXA',6)
    ,('GLBOXB',6)
    ,('GLBOXC',6)
    ,('GLBOXD',6)
    ,('GL',0)
    ,('GL0001',3)
    ,('GL0002',3)
    ,('GL0003',3)
    ,('GL0014',3)

SELECT   Location AS LocationCode
    ,LEFT(Location,CASE Ref WHEN 0 THEN LEN(Location) ELSE Ref - 1  END)
    ,RIGHT(Location,CASE Ref WHEN 0 THEN 0 ELSE LEN(Location) - Ref + 1 END)
FROM @LocationRef

enter image description here

Comments