How to remove strings between quotes on Microsoft SQL Server 2016?

Question

For column of string like this "This is comment 1""comment 2"3302, 0 or more comments between double quotes concatenated with an id of any length or characters of some item at the end. How to extract only the id so that the end result becomes 3302 on Microsoft SQL Server 2016?

Example Input:

Item                                                     Qty
2d3eu                                                    30
"This item is discontinued"103d2h                        20
"Just some random comment"1er3fhvd                       10
"There can be any number of comment""Like this"144       20

Desired output:

Item      Qty
2d3eu     30
103d2h    20
1er3fhvd  10
144       20

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, May 01 '23 at 00:06

joshAU · Answer 1 · 2023-05-01T00:33:02.613

If the ID will only ever be 4 characters long you could use comment = right(comment, 4)

However, if the length of the code may change, you will need to find the rightmost double quote character, and take the text after that. (Below assumes string is maximum of 100 characters, so adjust as needed. You will also need to enclose your original string with single quotes for sql to be happy with it).

Declare @Original_string as varchar(100)
Select @Original_string = '"This is comment 1""comment 2"3302'
Select NewString = reverse(left(reverse(@Original_string), charindex('"', reverse(@Original_string)) -1))

Taken from this other Stack Exchange answer by @Philip Kelley here: Find index of last occurrence of a sub-string using T-SQL

score 2 · Accepted Answer · answered May 01 '23 at 00:33

You can extract the Item number by finding the last " in the string and then taking all the characters to the RIGHT of that. Note we add a " to the beginning of the string to ensure that there is at least one in every value:

SELECT RIGHT(Item, CHARINDEX('"', REVERSE(CONCAT('"', Item))) - 1) AS Item, Qty
FROM stock

Output:

Item    Qty
00001   30
00002   20
00003   10
00004   20

Demo on dbfiddle

Yitzhak Khabinsky · Answer 3 · 2023-05-01T00:29:13.040

Please try the following solution.

It is based on tokenization via XML and XQuery.

The r[last()] predicate does all the magic.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(MAX));
INSERT @tbl (tokens) VALUES
('00001'),
('"This item is discontinued"00002'),
('"There can be any number of comment""Like this"00004');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '"';

SELECT t.*
    , result = c.value('(/root/r[last()]/text())[1]', 'VARCHAR(10)')
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c);

Output

ID	tokens	result
1	00001	00001
2	"This item is discontinued"00002	00002
3	"There can be any number of comment""Like this"00004	00004

How to remove strings between quotes on Microsoft SQL Server 2016?

3 Answers3