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
– Marshall Tigerus, Commented Jun 14, 2016 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
– João Amaro, Commented Jun 14, 2016 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
– user2366842, Commented Jun 14, 2016 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
– Joe Enos, Commented Jun 14, 2016 at 15:53

Matt · Accepted Answer · 2016-06-14 15:47:20Z

2

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

answered Jun 14, 2016 at 15:47

Matt

8025 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himanshu Jain · Accepted Answer · 2016-06-14 16:14:31Z

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, 2016 at 16:14

answered Jun 14, 2016 at 15:53

Himanshu Jain

5384 silver badges21 bronze badges

1 Comment

Himanshu Jain Over a year ago

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.

Collectives™ on Stack Overflow

Extract string from column sql?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related