Handling Strings in SQL Server

“How could I split a SQL field by a delimiter?”, “How could I get with the first word of a SQL field?”, “How could I get the first three letters of an SQL field? And the last three?”, “How could I join String variables?”

These are all questions that you might have done yourself if you often use SQL Server. We will try to answer them all in this post.

Splitting a String Field by Delimiter in SQL Server

In the case you need to split a String Field by a Delimiter, STRING_SPLIT is your function. If your value is fixed, you could do something like this:

SELECT value FROM STRING_SPLIT('a-b-c-d-e','-');

Now let’s say you want to count currencies:

SELECT value,count(*) FROM STRING_SPLIT('a-b-c-d-e-a-b-c-e-e','-') GROUP BY value;

This is useful if your string is fixed… But what if it is not? Let’s say you have a name and a middle name in the same String and you want to separe it, how would you do it?

Here is where SUBSTRING, CHARINDEX, and LEN come to your rescue:

SELECT SUBSTRING([name],1,CHARINDEX(' ',[name],1)) AS First_Name, SUBSTRING([name], CHARINDEX(' ',[name],1)+1, LEN([name])) AS Middle_Name FROM [Test].[dbo].[test];

And what if I have in a table string fields like this ‘a-b-c-d-e’ and I want to split each one of them:

Well, here it gets complicated, the best approach would be converting the strings to XML format and separating it by a delimiter, like this:

DECLARE @Delimiter CHAR = '-'

SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT CAST ('<M>' + REPLACE([name], @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
     FROM [Test].[dbo].[test]
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a) 

How could I get with the first word of a SQL field?

Here we could use SUBSTRING and CHARINDEX like we did before.

 SELECT SUBSTRING([name],1,CHARINDEX(' ',[name],1)) AS First_Name FROM [Test].[dbo].[test]

This will get you the First Name but there is a catch, what if the String doesn’t have a space (The user does not have a middle name)?

In this case, we could also use a CASE WHEN to determine if there is a space or not, your query will be something like this:

SELECT SUBSTRING([name],1,CASE WHEN CHARINDEX(' ',[name],1)!= 0 THEN CHARINDEX(' ',[name],1) ELSE LEN([name]) END) FROM [Test].[dbo].[test]

As you might know by now, as the data gets more extensive, our queries to determine what we need will get more complicated too because we will have to consider different situations.

How could I get the first three letters of an SQL field? And the last three?

Using LEFT and RIGHT will get us there:

SELECT RIGHT([name],3) FROM [Test].[dbo].[test]; /* Will give us the first three letters */

SELECT RIGHT([name],3) FROM [Test].[dbo].[test]; /* Will give us the last three letters */

And what if we want all the text EXCEPT the first letters or the last letters? We saw before how to use SUBSTRING and LEN, we could combine them here:

SELECT SUBSTRING([name],4,LEN([name])) FROM [Test].[dbo].[test]; /* Will give us everything except the first three letters */

SELECT SUBSTRING([name],1,LEN([name])-3) FROM [Test].[dbo].[test]; /* Will give us everything except the last three letters */

How could I join String variables?

If you are trying to join different columns of the same row, we could use CONCAT here.

SELECT CONCAT([name],' ',[lastname]) as FullName FROM [Test].[dbo].[test]

In the case you want to join the same column of different rows (Or any other), it could get a bit tricky, but something like this might work:

SELECT CONCAT([a].[first_row_name],';',[b].[second_row_name]) FROM
(SELECT T.* FROM (SELECT ROW_NUMBER() OVER (ORDER by name ASC) as tmpId, [name] as first_row_name FROM [Test].[dbo].[test]) T WHERE T.tmpId=1) a
LEFT JOIN
(SELECT T.* FROM (SELECT ROW_NUMBER() OVER (ORDER by name ASC) as tmpId, [name] as [second_row_name] FROM [Test].[dbo].[test]) T WHERE T.tmpId=2) b
on a.tmpId=1 and b.tmpId=2

Conclusion

Dealing with Strings in SQL server is not as easy as it might seem, if your tables are simple, probably you will have fewer problems dealing with them, but, as you might see, as your data starts to become more complex, your Queries will have to get more complicated too.

Hope you find this tutorial useful, maybe you could also learn more about SQL Server in this post.

See you the next round!

Share on Facebook

Tweet

Follow us