Extract string from column sql?

Question

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

TBH, I'd just do this operation in the application layer rather than the database layer. — Marshall Tigerus, Jun 14 '16 at 15:43
Yeah, I know it could be better. But in this case I really need a query in my db — João Amaro, Jun 14 '16 at 15:44
What specific type of SQL? That very likely will affect the answer. EDIT: Please add the appropriate tag for the SQL type to the question. — user2366842, Jun 14 '16 at 15:44
If all of your records are formatted like that, I'd definitely recommend cleaning that up and getting things into their own columns. If you can't do that, there are a few possible cheats - wrapping a view around it with all the substring/charindex nonsense so you can query it more easily - or go the opposite route: fix the data, dump it into a table with a different name, drop this table, and make an updatable view with the same name/columns as this table, where the insert/update trigger breaks up the data into the good columns in your actual table. — Joe Enos, Jun 14 '16 at 15:53

score 2 · Accepted Answer · answered Jun 14 '16 at 15:47

2

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

answered Jun 14 '16 at 15:47

Matt

782
4
11

Himanshu Jain · Answer 2 · 2016-06-14T16:14:31.817

1

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.

edited Jun 14 '16 at 16:14

answered Jun 14 '16 at 15:53

Himanshu Jain

518
4
20

It might be the possibility that you have some other patterned data in the same column So in that case it is breaking the statement. I have modified my answer, now you can take the latest script from their. – Himanshu Jain Jun 14 '16 at 16:13

Extract string from column sql?

2 Answers2

Linked