CODEWITHSUNDEEP
sqlsql-serverdelimitercharindex
Bb22
Bb22

Reputation: 23

Finding Substring With Delimiter SQL

Lets say I have a dimension, called "Custom1" that can be 47x29x10 , 01x8x300...etc.

How can I split on the "x" delimiter regardless of the length between the delimiters?

I was successfully able to capture the first and last numbers, but the middle number is giving me trouble.

Here is the code I have already

SELECT

TRIM(SUBSTRING(Custom1, 1, CHARINDEX('x',Custom1)-1)) as Length,

SUBSTRING(Custom1, CHARINDEX('x', Custom1) +1, LEN(Custom1) - CHARINDEX('x', Custom1)) as Widths,

REVERSE(SUBSTRING(REVERSE(Custom1),0,CHARINDEX('x',REVERSE(Custom1)))) as Height

FROM Table

Upvotes: -1

Views: 78

Answers (2)

Samer
Samer

Reputation: 161

SELECT
    PARSENAME(REPLACE(Custom1, 'x', '.'), 3) as Length,
    PARSENAME(REPLACE(Custom1, 'x', '.'), 2) as Width,
    PARSENAME(REPLACE(Custom1, 'x', '.'), 1) as Height
FROM YourTable;

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

Assuming no decimals...

This is similar to Samer's response, but here we use a CROSS APPLY to create the temp string once

Example

Select SomeCol 
      ,Pos1 = parsename(tStr,3)
      ,Pos2 = parsename(tStr,2)
      ,Pos3 = parsename(tStr,1)
 From YourTable
 Cross Apply ( values (replace(SomeCol,'x','.'))) B(tStr)

Now... If you have decimals, perhaps a little JSON instead?

Select A.[SomeCol]
       ,Pos1 = JSON_VALUE(JS,'$[0]')
       ,Pos2 = JSON_VALUE(JS,'$[1]')
       ,Pos3 = JSON_VALUE(JS,'$[2]')
 From  YourTable A
Cross Apply (values ('["'+replace(string_escape([SomeCol],'json'),'x','","')+'"]') ) B(JS)

Both Return

SomeCol     Pos1    Pos2    Pos3
47x29x10    47      29      10

Upvotes: 3

Related Questions