How to split string in SQL Server

Question

I'm trying to split the value from the model_interested column so that from the whole data only model display

SELECT SUBSTRING(model_interested, 20, CHARINDEX('model', model_interested)) AS model_interested 
FROM cust

From the image in model_interested column I wish to only display

"model":"A180"
"model":"A200 AMG FL"

I have tried splitting by number of characters but I don't think it's the right way.

If you're storing JSON in the database and you're using SQL Server 2016 or newer ... learn.microsoft.com/en-us/sql/relational-databases/json/… — ta.speot.is
– ta.speot.is, Commented Apr 22, 2019 at 9:09
Welcome to stackoverflow. Please take a minute to take the tour, especially How to Ask. Please read the first two paragraphs of the sql tag info and edit your question accordingly. — Zohar Peled
– Zohar Peled, Commented Apr 22, 2019 at 9:19

Sebastian Brosch · Accepted Answer · 2019-04-22 10:05:47Z

1

You can use the following solution using SUBSTRING and CHARINDEX:

SELECT SUBSTRING(model_interested, CHARINDEX('"model":"', model_interested) + LEN('"model":"'), CHARINDEX('"', model_interested, CHARINDEX('"model":"', model_interested) + LEN('"model":"')) - (CHARINDEX('"model":"', model_interested) + LEN('"model":"')))
FROM table_name

To get the whole property (with property name and value) you can use the following solution:

SELECT SUBSTRING(model_interested, CHARINDEX('"model":"', model_interested), CHARINDEX('"', model_interested, CHARINDEX('"model":"', model_interested) + LEN('"model":"')) - CHARINDEX('"model":"', model_interested) + 1)
FROM table_name

You can also use JSON_VALUE to get the expected value, but you have to change the data to a valid JSON value:

SELECT JSON_VALUE(REPLACE(REPLACE(model_interested, '{[', '[{'), ']}', '}]'), '$[0].model')
FROM table_name

demo on dbfiddle.uk

edited Apr 22, 2019 at 10:05

answered Apr 22, 2019 at 9:06

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

Comments

Deepshikha · Accepted Answer · 2019-04-22 10:31:21Z

0

If using earlier version of SQL server than 2016, then you can write a query as:

;with cte as
(
 SELECT   
       Split.a.value('.', 'VARCHAR(100)') AS model_interested  
 FROM  (
         SELECT CAST ('<M>' + REPLACE([model_interested], '.', '</M><M>') + '</M>' AS XML)
                as  model_interested_xml
         FROM  @cust

        ) AS A CROSS APPLY model_interested_xml.nodes ('/M') AS Split(a)  
    )
select model_interested
from cte
where CHARINDEX('model',model_interested) > 0

demo here: https://rextester.com/CCW41495

answered Apr 22, 2019 at 10:31

Deepshikha

10.3k2 gold badges24 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to split string in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related