CODEWITHSUNDEEP
sqlsql-servert-sqlsubstring
Jenifer
Jenifer

Reputation: 9

Substing - Get date from inbetween characters

I have a varchar column of variable size. The values in that column may differ e.g.

###LoggedIn 2021-03-30 16:09:10###
I have ###LoggedIn 2021-03-29 16:09:10### regularly
I am not sure if I ###LoggedIn 2021-03-28 16:09:10###

I wanted to get only the Date out of the string. I tried Substring, but it works only for the first scenario., I want a script which works for all scenarios. Not just this 3, but more.

select CAST(ISNULL(SUBSTRING('###LoggedIN 2021-03-04 16:09:10###', 13, 11),'1900-01-01')  AS date)

Upvotes: 0

Views: 59

Answers (3)

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

You can use substring with patindex

select convert(date,substring('###LoggedIN 2021-03-04 16:09:10###',patindex('%###LoggedIn %','###LoggedIN 2021-03-04 16:09:10###')+12,10))

From table

 select convert(date,substring(Column,patindex('%###LoggedIn %',Column)+12,10)) from YourTable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

The data that you have provided follows a very regular pattern. Presumably, you want the date after '###LoggedIn '. This means that you can just look for that pattern, go to the end and then take 19 characters:

select v.*,
       substring(col, charindex('###LoggedIn ', col) + 12, 19)
from (values ('###LoggedIn 2021-03-30 16:09:10###'),
             ('I have ###LoggedIn 2021-03-29 16:09:10### regularly'),
             ('I am not sure if I ###LoggedIn 2021-03-28 16:09:10###')
     ) v (col);

Upvotes: 0

Dale K
Dale K

Reputation: 27472

Use patindex to find known parts of the string. And then use substring to pull the date out.

select
    x.col1
    -- Find the end of the date and substring it. Convert to date.
    , convert(date, substring(y.col2, 1, patindex('%###%', y.col2)-1))
from (
    values
    ('###LoggedIn 2021-03-30 16:09:10###')
    , ('I have ###LoggedIn 2021-03-29 16:09:10### regularly')
    , ('I am not sure if I ###LoggedIn 2021-03-28 16:09:10###')
) x (Col1)
-- Find the start of the date, and substring it, use cross apply so we can use this multiple times
cross apply (values (substring(x.col1, patindex('%###LoggedIn %', x.col1)+12, len(x.col1)))) y (col2);

Upvotes: 2

Related Questions