CODEWITHSUNDEEP
sqlsql-serversql-server-2008
IRSHAD
IRSHAD

Reputation: 1622

Better SQL Query to split alphanumeric string based on pattern

I need to split alphanumeric strings like '20A30D40I10O' as below. The characters which are allowed : A,D,I,O in any order and its corresponding percentage.

+------+------+
| ColA | ColB |
+------+------+
|   20 | A    |
|   30 | D    |
|   40 | I    |
|   10 | O    |
+------+------+

I had tried to write the query for the same.

DECLARE @Val Varchar(100)
set @Val = '20A30D40I10O'

DECLARE @origTable TABLE(name VARCHAR(1000))
INSERT INTO @origTable(name) VALUES(@Val)

    SELECT SUBSTRING(name,1,2),SUBSTRING(name,3,1)FROM @origTable where name LIKE '%[0-9][0-9]%' and (SUBSTRING(name,3,1) LIKE 'A' OR SUBSTRING(name,3,1) LIKE 'D' OR SUBSTRING(name,3,1) LIKE 'I' OR SUBSTRING(name,3,1) LIKE 'O')
    UNION ALL
    SELECT SUBSTRING(name,4,2),SUBSTRING(name,6,1) FROM @origTable where name LIKE '%[0-9][0-9]%' and (SUBSTRING(name,6,1) LIKE 'A' OR SUBSTRING(name,6,1) LIKE 'D' OR SUBSTRING(name,6,1) LIKE 'I' OR SUBSTRING(name,6,1) LIKE 'O')
    UNION ALL
    SELECT SUBSTRING(name,7,2),SUBSTRING(name,9,1) FROM @origTable where name LIKE '%[0-9][0-9]%' and (SUBSTRING(name,9,1) LIKE 'A' OR SUBSTRING(name,9,1) LIKE 'D' OR SUBSTRING(name,9,1) LIKE 'I' OR SUBSTRING(name,9,1) LIKE 'O')
    UNION ALL
    SELECT SUBSTRING(name,10,2),SUBSTRING(name,12,1) FROM @origTable where name LIKE '%[0-9][0-9]%' and (SUBSTRING(name,12,1) LIKE 'A' OR SUBSTRING(name,12,1) LIKE 'D'OR SUBSTRING(name,12,1) LIKE 'I' OR SUBSTRING(name,12,1) LIKE 'O')

But this query is not sufficient for splitting the string if it contains 1 digit percentages. eg: '98D2O'

Upvotes: 0

Views: 2410

Answers (2)

Veera
Veera

Reputation: 3492

I made a work around. But it is late I know. I just want to share the idea what I done. 

CREATE TABLE #Temp(ColA INT, ColB VARCHAR(20))

DECLARE @TempString VARCHAR(100)
SET @TempString = '20A30D40I10O'
DECLARE @TempVAL VARCHAR(10)
DECLARE @Position INT
DECLARE @Col1Val VARCHAR(10)
SET @Position = 1

WHILE (@Position <= LEN(@TempString))
BEGIN
  SET @TempVAL = SUBSTRING(@TempString, @Position, 1)

  IF(ISNUMERIC(@TempVAL) = 1)
     SET @Col1Val = ISNULL(@Col1Val, '') + @TempVAL
  ELSE
  BEGIN
     INSERT INTO #Temp VALUES(@Col1Val, @TempVAL) 

     SET @Col1Val = NULL
  END
  SET @Position = @Position + 1

END

SELECT * FROM #Temp

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Use while loop with some string functions. Might not be the best way to do it but should work. Try this.

DECLARE @Val  VARCHAR(100),@cnt  INT =1,@temp VARCHAR(50),@len  INT,@intr INT=1

SET @Val = '20A30D40I10O'
SET @len = Len(@Val)

DECLARE @origTable TABLE
  (name VARCHAR(1000))

WHILE @cnt <= @len
  BEGIN
      SET @temp =Substring(@Val, @cnt, 1)
      IF @temp LIKE '[a-z]'
        BEGIN
            INSERT INTO @origTable
            SELECT Substring(@Val, @intr, ( @cnt - @intr ) + 1)

            SET @intr = @cnt + 1
        END
      SET @cnt=@cnt + 1
  END

SELECT LEFT(name, Patindex('%[a-z]%', name) - 1) Cola,
       Substring(name, Patindex('%[a-z]%', name), Len(name)) Colb
FROM   @origTable

Upvotes: 1

Related Questions