Saving Substring's from a Cross Apply into A Variable

Question

I need to transform a column returning '12 x 18 x 18 in' into 3 different columns by just grabbing the 12 18 18.

I need to store these into variables to do an insert further down because this is part of a large stored procedure doing data translation from one DB to another.

Using an example from a previous question I created this:

        DECLARE     
        @HeightT VARCHAR(10),
        @LengthT VARCHAR(10),
        @WidthT VARCHAR(10)


        SELECT TOP 1 ParsedData.* 
        FROM DB.dbo.Table sh
        CROSS apply ( select str = sh.Dimensions + 'xxx in' ) f1
        CROSS apply ( select p1 = charindex( 'x', str ) ) ap1
        CROSS apply ( select p2 = charindex( 'x', str, p1 + 1 ) ) ap2
        CROSS apply ( select p3 = charindex( 'x', str, p2 + 1 ) ) ap3
        CROSS apply ( select substring( str, 1, p1-1 )   Height               
             ,   substring( str, p1+1, p2-p1-1 ) Length
             ,   substring ( str, p2+1, p3 -p2 - 3) Width
             WHERE ID = '1111111'

         ) 
         ParsedData

-From How to split a comma-separated value to columns

Is it possible to to grab the Height, Length and Width to store into those variables so I can then use them in an insert later on in the stored procedure?

*Note this does work as far as just selecting the parsed values, but this is not what I need. The cross apply is the part giving me difficulties.

Answer:

Using T I's suggestion I made this:

DECLARE @tbl TABLE (col VARCHAR(255))
INSERT INTO @tbl (col)
SELECT Dimensions
FROM DB.dbo.Table
WHERE ID = '1111111'

DECLARE @Height INT, @Length INT, @Width INT, @Temp varchar(5)

SELECT TOP 1
@Height = CAST(LEFT(col, CHARINDEX('x', col)-1) AS int),
@Length  = CAST(SUBSTRING(col, CHARINDEX('x', col)+1, CHARINDEX('x', REVERSE(col))-CHARINDEX('x', col)) AS int),
@Temp  = CAST(REVERSE(LEFT(REVERSE(col), CHARINDEX('x', REVERSE(col))-2)) AS VARCHAR(8)) 
FROM @tbl

SET  @Width = SUBSTRING(@Temp,1,(CHARINDEX(' ',@Temp + ' ')-1) )

SELECT Height = @height, Length = @Length, Width = @Width

T I · Accepted Answer · 2015-12-22T21:09:45.403

0

This can be done without cross apply.

DECLARE @tbl TABLE (col VARCHAR(255))
INSERT INTO @tbl (col)
VALUES ('18 x 18 x 12')

DECLARE @height INT, @width INT, @depth INT

SELECT TOP 1
    @height = CAST(LEFT(col, CHARINDEX('x', col)-1) AS int),
    @width  = CAST(LEFT(STUFF(col, 1, CHARINDEX('x', col)+1, ''), CHARINDEX('x', STUFF(col, 1, CHARINDEX('x', col)+1, ''))-1) AS int),
    @depth  = CAST(REVERSE(LEFT(REVERSE(col), CHARINDEX('x', REVERSE(col))-1)) AS int) 
FROM @tbl

SELECT height = @height, width = @width, depth = @depth

edited Dec 22 '15 at 21:09

answered Dec 22 '15 at 21:01

T I

9,785
4
29
51

Thank you! I figured I might have to go the route of a temp table. I appreciate the help. Your answer was spot on. I just had to do another temp variable to save the '18 in' portion, and substring that into an 18 so it can then be stored as an int. – Charles.S Dec 22 '15 at 21:21

Saving Substring's from a Cross Apply into A Variable

*Note this does work as far as just selecting the parsed values, but this is not what I need. The cross apply is the part giving me difficulties.

1 Answers1