SQL Parse string to get specific values

Question

Hi I have this value in a column

11-1-11111-349b65eda2-4f8e-413d-b76a-6a2c13d6e494OctWed0800422015-request-response.xml

How would I parse this using SQL to get only:

11-1-11111-349b65eda2-4f8e-413d-b76a-6a2c13d6e494OctWed0800422015

tell us what is the logic.for parse the string. Remove the text after 8th - ? Or maybe remove after length string > 65 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 7, 2015 at 15:13

Tim Schmelter · Accepted Answer · 2015-10-07 15:21:42Z

1

In SQL-Server you could use this approach if the right side is always -request-response.xml:

SELECT FileName,
       Leftpart = LEFT(FileName,CHARINDEX('-request-response.xml',FileName)-1)
FROM MyTable

Demo

This is more fail-safe:

WITH CTE AS
(
   SELECT FileName,
          Length = CHARINDEX('-request-response.xml',FileName) -1
    FROM MyTable
)
SELECT LeftPart = CASE WHEN Length < 0 THEN NULL 
                   ELSE LEFT(FileName, Length) END
FROM CTE

edited Oct 7, 2015 at 15:21

answered Oct 7, 2015 at 15:16

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

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

4 Comments

Gordon Linoff Over a year ago

If you happen to be using a database that supports left() and charindex(), which pretty much means SQL Server or Sybase.

Tim Schmelter Over a year ago

@GordonLinoff: i've added that this is for sql-server. Thought that it was but OP didn't mention it.

Jerry Trac Over a year ago

I get the error: Msg 537, Level 16, State 3, Line 2 Invalid length parameter passed to the LEFT or SUBSTRING function.

Tim Schmelter Over a year ago

@JerryTrac: if the value doesnt contain the search word you'll get a negative length which is not allowed in LEFT. That's why i've added the second approach.

Collectives™ on Stack Overflow

SQL Parse string to get specific values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related