question

Kay0807 avatar image
Kay0807 asked

How to parse out string value between two special charaters?

Hello,

I have a column called comments (varchar). I need to parse value from it. Comment= "Today is nice day.:I love it:" so now I need to parse string between two ':' characters. Which is - I love it

sql-server-2017
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What have you tried so far?

0 Likes 0 ·

@Kay0807 What do you need to happen when the comments have more than 2 sentences? It looks like it goes without saying that if the second sentence is not terminated with the dot then nothing should be returned, but what about the scenario with more than 2 sentences? Please clarify.

0 Likes 0 ·

Or, indeed, if the phrase has zero or one delimiter?

0 Likes 0 ·

SQL Server 2017? And you're looking to string split function... have you tried STRING_SPLIT that's built into the language from 2016 onwards?

0 Likes 0 ·

1 Answer

· Write an Answer
lokeshlehkara avatar image
lokeshlehkara answered

Try This.

But this will work, only for case when you have two ":" . In case of multiple ":" you need to convert this into dynamic query

DECLARE @n varchar(50) = 'Today is nice day.:I Love it:'
Declare @TotalL int = (SELECT LEN(@n)) /* Total Length of String */
Declare @Ft int
SELECT @Ft = LEN(LEFT(@n,CHARINDEX(':',@n)+1)) /* Identify First ":" in string */
Declare @m varchar(50) = (SELECT SUBSTRING(@n,@Ft,@TotalL)) /* Extract SubString for next ":" */
Declare @Sec int
SELECT @Sec = LEN(LEFT( @m ,CHARINDEX(':',@m)+1)) /* Identify second ":" */
SELECT SUBSTRING(@n,@Ft,@Sec)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.