CODEWITHSUNDEEP
sqlsql-servert-sql
Noelle
Noelle

Reputation: 782

Extract string between double quotes in SQL

I have a string and I need to return the characters between the double quotes

'(("Name 1" and "Name 2") or "Name 3")

I need to return

Name 1
Name 2
Name 3

I have used the function below to split the string but I am getting the (( and the Or and AND etc .. which I don't want and unfortunately I can't be certain of all the other characters that could be included so removing them or replacing them isn't really feasible.

ALTER FUNCTION [dbo].[fn_SplitString] 
( 
   @string NVARCHAR(MAX), 
   @delimiter CHAR(1) 
) 
RETURNS @output TABLE(ID int, splitdata NVARCHAR(MAX) 
) 
BEGIN 
   DECLARE @start INT, @end INT, @Count INT
   set @Count = 1
   SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
   WHILE @start < LEN(@string) + 1 BEGIN 
      IF @end = 0  
         SET @end = LEN(@string) + 1

       INSERT INTO @output (ID, splitdata)  
       VALUES(@Count, SUBSTRING(@string, @start, @end - @start)) 
       SET @start = @end + 1 
       SET @end = CHARINDEX(@delimiter, @string, @start)
       Set @Count = @Count+1
   END 
   RETURN 
END

I know this code will return the string between 2 delimiters 

substring( LEFT(@String, charindex(']', @String)-1), CHARINDEX('[', @String) + len('['), LEN(@String))

is there any way to combine the 2 and return the required output?

Thanks

Upvotes: 2

Views: 7285

Answers (5)

Vasily
Vasily

Reputation: 5782

additional variant, avoiding loops:

DECLARE @testData VARCHAR(250) = '(("Name 1" and "Name 2") or "Name 3")'
DECLARE @delimeter VARCHAR(10) = '"';

;WITH
    tally AS --create sequence 1 to string Length
    (
        SELECT n = 1
        UNION ALL
        SELECT n +1
        FROM tally
        WHERE n <= LEN(@testData)
    ),
    cte AS
    (
        SELECT T.N , ROW_NUMBER() OVER ( ORDER BY T.N ) AS RN
        FROM tally AS T
        WHERE SUBSTRING(@testData, T.N, LEN(@delimeter)) = @delimeter
    )
SELECT REPLACE(SUBSTRING(@testData, COALESCE(R.N, LEN(@delimeter), 1),
                            L.N - COALESCE(R.N, LEN(@delimeter), 1)),@delimeter,'') AS part
FROM cte AS L
    LEFT JOIN cte AS R ON L.RN = R.RN+1
WHERE L.RN%2=0
OPTION (MAXRECURSION 1000)

Upvotes: 0

Edmond Quinton
Edmond Quinton

Reputation: 1739

This modified version of the Jeff Moden's splitter function should give you what you are looking. It avoids loops and the need to find and replace certain strings or characters.

CREATE FUNCTION dbo._SplitStrings
(
    @List NVARCHAR(MAX),
    @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    WITH E1(N)
    AS 
    ( 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ),
    E2(N) AS    ( SELECT 1 FROM E1 a, E1 b ),
    E4(N) AS    ( SELECT 1 FROM E2 a, E2 b ),
    E42(N) AS   ( SELECT 1 FROM E4 a, E2 b ),
    cteTally(N)  
    AS 
    (
        SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0)),
  cteSplitByDelimiter AS
  (
      SELECT    s.N1, 
                SUBSTRING(@List, s.N1 - 1, 1) AS [StartDelimiter],
                SUBSTRING(@List , s.N1 + ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000), + 1) AS [EndDelimiter],
                SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000)) AS [Item]
        FROM cteStart s
  ),
  cteList AS
  (
      SELECT N1
            ,Item
            ,ROW_NUMBER() OVER (ORDER BY N1) % 2 AS ExpectedItem
      FROM  cteSplitByDelimiter sd
      WHERE sd.StartDelimiter = @Delimiter and sd.EndDelimiter = @Delimiter
  )
  SELECT    Item
  FROM      cteList 
  WHERE     ExpectedItem = 1;

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This is a recursive approach

DECLARE @s VARCHAR(100)='(("Name 1" and "Name 2") or "Name 3")';

WITH recCTE AS
(
    SELECT 1 AS Position
          ,SUBSTRING(@s,1,1) AS CharAtPos
          ,CASE WHEN SUBSTRING(@s,1,1)='"' THEN 0 ELSE -1 END AS QuoteGroup
          ,CASE WHEN SUBSTRING(@s,1,1)='"' THEN 1 ELSE 0 END AS QuoteIsOpen
    UNION ALL
    SELECT r.Position+1
          ,SUBSTRING(@s,r.Position+1,1)
          ,CASE WHEN SUBSTRING(@s,r.Position+1,1)='"' THEN CASE WHEN r.QuoteIsOpen=0 THEN r.QuoteGroup+1 ELSE r.QuoteGroup END ELSE r.QuoteGroup END AS QuoteGroup
          ,CASE WHEN SUBSTRING(@s,r.Position+1,1)='"' THEN CASE WHEN r.QuoteIsOpen=0 THEN 1 ELSE 0 END ELSE r.QuoteIsOpen END AS QuoteIsOpen
    FROM recCTE r
    WHERE r.Position+1<=LEN(REPLACE(@s,' ','*')) 
)
SELECT r.QuoteGroup
      ,(
        SELECT CharAtPos AS [*]
        FROM recCTE r2 
        WHERE r2.QuoteGroup=r.QuoteGroup AND r2.QuoteIsOpen=1 AND r2.CharAtPos<>'"'
        ORDER BY r2.Position
        FOR XML PATH(''),TYPE).value('.','varchar(100)')
FROM recCTE r
WHERE r.QuoteGroup>=0
GROUP BY QuoteGroup;

Some explanation:

The recursive CTE will run through your string character by character. It will check for quotes and keep track of being open or being closed. Depending on this, all values with an open flag are grouped and re-concatenated via XML.

Upvotes: 7

Rubens Farias
Rubens Farias

Reputation: 57966

Give this a try; I kept the debugging variables in the output table

ALTER FUNCTION [dbo].[fn_SplitString] 
( 
    @string     NVARCHAR(MAX), 
    @delimiter  CHAR(1) 
) 
RETURNS @output TABLE
(
    [Id]        INT,
    [Start]     INT,
    [End]       INT,
    [Length]    INT,
    [Data]      NVARCHAR(MAX)
) 
BEGIN 
    DECLARE @count INT, @start INT, @end INT
    SELECT  @count = 1, @end = 0,
            @start = CHARINDEX(@delimiter, @string)
    WHILE   @start > 0 BEGIN 

        SELECT  @end = CHARINDEX(@delimiter, @string, @start + 1)

        INSERT INTO @output ([Id], [Start], [End], [Length], [Data])
        VALUES (@count, @start, @end, @end - @start - 1, 
                SUBSTRING(@string, @start + 1, @end - @start - 1)) 

        SELECT  @start = CHARINDEX(@delimiter, @string, @end + 1), 
                @count = @count + 1
    END 
    RETURN 
END

Or this, based on the idea of receiving two different delimiters:

ALTER FUNCTION [dbo].[fn_SplitString] 
( 
    @string     NVARCHAR(MAX), 
    @delimiter1 NVARCHAR(MAX),
    @delimiter2 NVARCHAR(MAX)
) 
RETURNS @output TABLE
(
    [Id]        INT,
    [Start]     INT,
    [End]       INT,
    [Length]    INT,
    [Data]      NVARCHAR(MAX)
) 
BEGIN 
    DECLARE @count INT, @start INT, @end INT
    SELECT  @count = 1, @end = 0
    SELECT  @start = CHARINDEX(@delimiter1, @string)
    SELECT  @end   = CHARINDEX(@delimiter2, @string, @start + 1)

    WHILE   @start > 0 AND @end > 0 BEGIN 

        INSERT INTO @output ([Id], [Start], [End], [Length], [Data])
        VALUES (@count, @start, @end, @end - @start - 1, 
                SUBSTRING(@string, @start + 1, @end - @start - 1)) 

        SELECT  @start = CHARINDEX(@delimiter1, @string, @end + 1)
        SELECT  @end   = CHARINDEX(@delimiter2, @string, @start + 1),
                @count = @count + 1
    END 
    RETURN 
END

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need to use replace() function & then pass it to function parameter :

declare @string varchar(255) = '(("Name 1" and "Name 2") or "Name 3")'

set @string = replace(replace(replace(replace(replace(@string, '(', ''), ')', ''), '"', ''), 'and', ','), 'or', ',')

select *
from [dbo].[fn_SplitString](@string, ',') spt;

If you are using lastest version of SQL Server then you can use TRANSLATE() instead of instead replace().

Upvotes: 0

Related Questions