CODEWITHSUNDEEP
sql-server-express
João Amaro
João Amaro

Reputation: 496

Extract string from column sql?

So I have this column called MESSAGE on my LOGS Table that has every line starting like this (well, at least they all start with the word Percentage):

Percentage|15/25|1%*1569ms#0ms*C:\Snapshot\Snapshot_15.jpg

I need to select the string 

1569ms

in this case. They always come between * and #. How can I do this?

SELECT SUBSTRING(MESSAGE, , ) as Duration FROM LOGS

Upvotes: 0

Views: 13613

Answers (2)

Himanshu Jain
Himanshu Jain

Reputation: 538

Use below mentioned SQL query to fetch the output specified by you:

SELECT DISTINCT CASE 
        WHEN NAME IS NOT NULL
            AND len(NAME) > 40
            AND (CHARINDEX('#0ms', NAME) - CHARINDEX('|1%*', NAME) - 4) > 0
            THEN SUBSTRING(NAME, CHARINDEX('|1%*', NAME) + 4, (CHARINDEX('#0ms', NAME) - CHARINDEX('|1%*', NAME) - 4))
        ELSE ''
        END
FROM dbo.Table_1

We have in-build function in SQL to break the string(SUBSTRING()) or to fetch the index of any character with in a string(CHARINDEX), so by using both function we can easily find out the exact syntax as per our requirement.

Upvotes: 1

Matt
Matt

Reputation: 802

SELECT SUBSTRING(MESSAGE,
            CHARINDEX('*',message)+1,
            CHARINDEX('#',message)-CHARINDEX('*',message)-1) as Duration 
FROM LOGS

Upvotes: 2

Related Questions