locked
SQL Server 2012 extract string between known number of delimiters RRS feed

  • Question

  • Hi I would like to be able to  from a string a value as shown below - this can be done either in a query if really needed a function

    '2001||27700||339367.91|494261.74|NULL||'

    I would like to extract the value 339367.91 which will be between the 4 and 5 |

    The query can then be reused to extract the value 494261.74 which occurs between the 5 and 6 |

    this is using 2012.

    Regards

    Thursday, July 12, 2018 3:16 PM

Answers

  • You can use that function to get the values for each row from a table, for example

    Declare @Sample Table(FID int, GEOMETRY varchar(50));
    Insert @Sample(FID, GEOMETRY) Values
    (12345, '2001||27700||339367.91|494261.74|NULL||'),
    (98765, '2001||27700||339383.02|494999.00|NULL||');
    
    Select s.FID, s.GEOMETRY, a.Element5, a.Element6
    From @Sample s
    Cross Apply(
       Select Max(Case When ID = 5 Then Element End) As Element5,
       Max(Case When ID = 6 Then Element End) As Element6 
       From dbo.Split(s.GEOMETRY, '|')
       Where ID In (5,6)) a;
    Tom
    • Marked as answer by aivoryuk Thursday, July 12, 2018 7:04 PM
    Thursday, July 12, 2018 4:26 PM

All replies

  • I would recommend you use a Split function that returns both the value and the position of that value.  For example,

    CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table
    /* Use Option(MaxRecursion 0) in queries that call this function if 
       there can be more than 99 delimited values in @DelimitedString */
    AS
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces
      )
    GO

    Once you have that, you only need

    Declare @Sample varchar(50);
    Set @Sample = '2001||27700||339367.91|494261.74|NULL||'
    Select ID, Element
    From dbo.Split(@Sample, '|')
    Where ID In (5,6);

    to get both of the values you want.

    Tom

    Thursday, July 12, 2018 3:35 PM
  • As long as you dont have any special characters coming within the delimited values you can use a method like this

    https://visakhm.blogspot.com/2013/05/get-nth-positioned-string-from.html

    Pass any value for n and you will get value at that position


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, July 12, 2018 3:54 PM
  • Thanks Tom

    This column is already part of a table so I want to make it part of this so can I link the table function back to the original table


    • Edited by aivoryuk Thursday, July 12, 2018 4:03 PM
    Thursday, July 12, 2018 4:02 PM
  • Thanks Tom

    This column is already part of a table so I want to make it part of this so can I link the table function back to the original table


    you need to do like this

    DECLARE @N int = 5 -- to get 5th value (339367.91)
    
    SELECT CAST('<Root><Node>' + REPLACE(GEOMETRY,'|','</Node><Node>') + '</Node></Root>' AS xml).query('/Root/Node[position()= sql:variable("@N")]').value('.','varchar(50)') AS YourValue
    FROM YourTable t



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page




    • Edited by Visakh16MVP Thursday, July 12, 2018 4:19 PM
    Thursday, July 12, 2018 4:17 PM
  • You can use that function to get the values for each row from a table, for example

    Declare @Sample Table(FID int, GEOMETRY varchar(50));
    Insert @Sample(FID, GEOMETRY) Values
    (12345, '2001||27700||339367.91|494261.74|NULL||'),
    (98765, '2001||27700||339383.02|494999.00|NULL||');
    
    Select s.FID, s.GEOMETRY, a.Element5, a.Element6
    From @Sample s
    Cross Apply(
       Select Max(Case When ID = 5 Then Element End) As Element5,
       Max(Case When ID = 6 Then Element End) As Element6 
       From dbo.Split(s.GEOMETRY, '|')
       Where ID In (5,6)) a;
    Tom
    • Marked as answer by aivoryuk Thursday, July 12, 2018 7:04 PM
    Thursday, July 12, 2018 4:26 PM
  • THanks Tom that is pretty awesome very quick as well
    Thursday, July 12, 2018 7:04 PM