CODEWITHSUNDEEP
sql-server
안녀엉
안녀엉

Reputation: 35

How to use split in SQL Server?

I want to split first word in update query.

-- TITLE: "Visual Studio"

UPDATE SET TITLE = TITLE.Split(' ')[0]

-- RESULT: "Visual"

Upvotes: 2

Views: 297

Answers (4)

Nick Allan
Nick Allan

Reputation: 406

Check your compatibility level. If you have 130 or above, you can make use of T-SQL's latest STRING_SPLIT function, with the ordinal parameter.

E.g.

WITH cteExamples
    AS
    (
    SELECT StringToSplit = 'Visual Studio'
    UNION ALL SELECT 'Jet Brains'
    )
    SELECT e.*,
        FirstValue = s.[value]
        FROM cteExamples AS e
            CROSS APPLY STRING_SPLIT(e.StringToSplit, ' ', 1) AS s
        WHERE s.ordinal = 1;

Upvotes: 0

Zhorov
Zhorov

Reputation: 30003

If you use SQL Server 2016+, another possible approach to split a string into substrings and get a substring by position, is to use OPENJSON() (it's not too complicated, even if you are not familiar with JSON).

First, transform input text into valid JSON array using REPLACE() and STRING_ESCAPE() ('Visual studio' into '["Visual", "Studio"]' for example). After that, use OPENJSON() with default schema to retrieve this JSON array as table, which has columns key, value and type. The key column contains the index of the element in the specified array, so you can get each element by index.

Input:

CREATE TABLE #Data (
   [Text] nvarchar(100)
)
INSERT INTO #Data
   ([Text])
VALUES
   (N'Visu"al Studio'),
   (N'Visual Studio'),
   (N'Visual1 Studio'),
   (N'Visual2 Studio'),
   (N'Visual3 Studio'),
   (N'Visual[ Studio')

T-SQL:

UPDATE #Data
SET [Text] = (
   SELECT [value]
   FROM OPENJSON(CONCAT(N'["', REPLACE(STRING_ESCAPE([Text], 'json'), N' ', N'","'), '"]'))
   WHERE [key] = 0
   -- or get other substring by index: WHERE [key] = 1
)

Output:

SELECT [Text] 
FROM #Data

-------
Text
-------
Visu"al
Visual
Visual1
Visual2
Visual3
Visual[

Upvotes: 0

Igor Cova
Igor Cova

Reputation: 3524

In MS SQL Server to solve it use STRING_SPLIT like there:

update t set 
  t.Title = a.First_Word
from Your_Table
outer apply (
  select top 1
    value as First_Word
  from t.TITLE.STRING_SPLIT(' ')
) as a

Or you can write some inline function for this:

create function Get_FirstWord(@words varchar(max))
returns varchar(max) as
begin
  declare @First_word varchar(max);

  select top 1
    @First_word = value
  from @words.string_split(' ');

  return @First_word;
end;

And later use it like that:

update Your_Table set 
  Title = Get_First_Word(Title)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

There is no SPLIT function in SQL Server as far as I know. There is a STRING_SPLIT function, in version 2016 or later, but that would not necessarily work here. The reason it might not work as expected is that it generates a table as output, containing the individual terms, which are not ordered.

If you want to update the TITLE field to retain just the first word, consider using the base string functions:

UPDATE yourTable
SET TITLE = SUBSTRING(TITLE, 1, CHARINDEX(' ', TITLE) - 1)
WHERE CHARINDEX(' ', TITLE) > 0;

The above logic handles the case where a given title might not have any spaces in it, i.e. it consists of just one word. In this case, the update would just skip that record.

Upvotes: 7

Related Questions