Extracting a string between two variable points in SQL Server

Question

I have a column which should contain text relatively similar to this

Storage Location: E10-2
Die Area: 28.90

Really all I care about is the "E10-2" portion.

The code I am posting below is the best I have come up with and I am cringing just looking at it.

First off, I'm relying on the fact that the lines have not been reversed. Second, I'm relying on two spaces preceding and a carriage return following the location. Finally this a horribly long and cumbersome select string.

I looked into using dynamic SQL but that seemed even more cumbersome given the size of the size of the "FROM" statement.

Am I wrong? Is there a more elegant way to accomplish this? Possibly validating part of the "Storage Location:" line so I know I'm looking at the right line of the field?

SELECT SUBSTRING(
    CONVERT(VARCHAR(MAX),CONVERT(BINARY(8000),RB.BITS)), 
    CHARINDEX(' ',CONVERT(VARCHAR(MAX),CONVERT(BINARY(8000),RB.BITS)),CHARINDEX(' ',CONVERT(VARCHAR(MAX),CONVERT(BINARY(8000),RB.BITS)))+1),
    CHARINDEX(CHAR(10),CONVERT(VARCHAR(MAX),CONVERT(BINARY(8000),RB.BITS)))-CHARINDEX(' ',CONVERT(VARCHAR(MAX),CONVERT(BINARY(8000),RB.BITS)) ,CHARINDEX(' ',CONVERT(VARCHAR(MAX),CONVERT(BINARY(8000),RB.BITS)))+1))
FROM  
    TLS.DBO.REQUIREMENT R
INNER JOIN 
    TLS.DBO.REQUIREMENT_BINARY RB ON R.WORKORDER_BASE_ID = RB.WORKORDER_BASE_ID 
                                  AND R.WORKORDER_LOT_ID = RB.WORKORDER_LOT_ID 
                                  AND R.WORKORDER_SPLIT_ID = RB.WORKORDER_SPLIT_ID 
                                  AND R.WORKORDER_SUB_ID = RB.WORKORDER_SUB_ID 
                                  AND R.PIECE_NO = RB.PIECE_NO
WHERE 
    R.WORKORDER_BASE_ID = 'FP-01405-0032' AND
    R.PART_ID LIKE 'PCH%'

It's hard to tell without knowing all the possible legal formats of the string you're trying to parse... but pulling out everything after "Storage Location: " up to and not including a CR or LF or space character (i.e. whitespace) seems reasonable. I'm not sure what type RB.BITS starts out as... or why you're casting to BINARY (8000)... maybe you could explain that? — pmbAustin
– pmbAustin, Commented Apr 5, 2017 at 19:21
The field I am parsing is encoded as an image. Yes, I could use a much smaller value than 8000 and I most likely will once I am finished fine tuning this qwery, however many of our fields are much larger. Some approaching 8000 characters. I believe 8000 is the max and I just use it as boilerplate code. — mreff555
– mreff555, Commented Apr 5, 2017 at 19:25
You should probably just use varbinary(max) and cast that to varchar(max) in that case. It's a cleaner conversion with no chance of truncation. — pmbAustin
– pmbAustin, Commented Apr 5, 2017 at 19:44
I am running SQL-Server 2008, I'm not sure if that functionality was enabled in a later version, but I can't use the keyword max. — mreff555
– mreff555, Commented Apr 5, 2017 at 19:46

Laughing Vergil · Accepted Answer · 2017-04-05 21:00:53Z

1

I like using a CTE to reduce the number of CONVERTs and CHARINDEXes as a first step:

;WITH TextStr AS (
    SELECT 
        Substring(
            Charindex('Storage Location:', 
                CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), RB.BITS) ) + 17), DATALENGTH(RB.BITS)) as WorkStr
    FROM TLS.DBO.REQUIREMENT R
    INNER JOIN TLS.DBO.REQUIREMENT_BINARY RB ON
        R.WORKORDER_BASE_ID = RB.WORKORDER_BASE_ID AND
        R.WORKORDER_LOT_ID = RB.WORKORDER_LOT_ID AND
        R.WORKORDER_SPLIT_ID = RB.WORKORDER_SPLIT_ID AND
        R.WORKORDER_SUB_ID = RB.WORKORDER_SUB_ID AND
        R.PIECE_NO = RB.PIECE_NO
    WHERE 
        R.WORKORDER_BASE_ID = 'FP-01405-0032' AND
        R.PART_ID LIKE 'PCH%'
    )
SELECT 
    LTRIM(RTRIM(LEFT(WorkStr, CHARINDEX(CHAR(10), WorkStr + CHAR(10))-1)))
FROM TextStr

Basically, I search for your line's label, skip past it, and return a string containing all text after that point. Then I find the next Newline in the resulting string, and split the text there, retaining everything before the newline.

Assumptions made:

Char(10) is your NewLine indicator
The last line may or may not have a NewLine
The Image field could theoretically have more than 8K Bytes in it

Hope this helps.

EDIT: Here is fixed code for the query:

;WITH TextStr AS (
    SELECT 
        Substring(CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), RB.BITS)),
            Charindex('Storage Location:', 
                CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), RB.BITS) ) + 17), DATALENGTH(RB.BITS)) as WorkStr
    FROM TLS.DBO.REQUIREMENT R
    INNER JOIN TLS.DBO.REQUIREMENT_BINARY RB ON
        R.WORKORDER_BASE_ID = RB.WORKORDER_BASE_ID AND
        R.WORKORDER_LOT_ID = RB.WORKORDER_LOT_ID AND
        R.WORKORDER_SPLIT_ID = RB.WORKORDER_SPLIT_ID AND
        R.WORKORDER_SUB_ID = RB.WORKORDER_SUB_ID AND
        R.PIECE_NO = RB.PIECE_NO
    WHERE 
        R.WORKORDER_BASE_ID = 'FP-01405-0032' AND
        R.PART_ID LIKE 'PCH%'
    )
SELECT 
    LTRIM(RTRIM(LEFT(WorkStr, CHARINDEX(CHAR(10), WorkStr + CHAR(10))-1)))
FROM TextStr

edited Apr 5, 2017 at 21:00

answered Apr 5, 2017 at 19:39

Laughing Vergil

3,7661 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mreff555 Over a year ago

I didn't think you could do that with charindex. I've also never seen a query done like this. However I am still having issues with this. when I try to run your query it complains because there are only two substring arguments. When I add the converted RB.bits into the first parameter I get a conversion error from varchar to int.

Laughing Vergil Over a year ago

Hm. You are right. I messed up the code. I was trying to make the code even simpler, and I broke it.

Collectives™ on Stack Overflow

Extracting a string between two variable points in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related