pulling part of a string from SQL database column

Question

I have a table that has a column called "Memo". There is data in all the rows that look kinda like:

blah blah 08-22-19 Transfer from blah blah blah

I am needing to pull out just the "08-22-19 Transfer from". Ideally I would like to pull the info in a temp table with 2 different columns one named "Date" to enter 08-22-2019 and the other "Note" to enter Transfer from. I am having a hard time figuring how to get this done.

The format of the date is always mm-dd-yy but the date can be different — it could be 08-22-19 or 07-29-19.

Basically if table.memo had a row entry that was

The day that I did this 08-05-19 - AUDIT TRANSFERRED FROM 073 TO 057 because I wanted to.

I would like to be able to pull and insert into a new table like....

newtable.Date = 08/22/2019    
newtable.Note = AUDIT TRANSFERRED FROM 073 TO 057

What comes before the info I want to pull is all different and so is the following text. There is no pattern to the data before or after the text that I need to pull.

Use SUBSTRING function to get part of string.w3schools.com/sql/func_sqlserver_substring.asp — Chetan
– Chetan, Commented Aug 23, 2019 at 0:50
As described, this is not a simple problem: Sounds like you need to recognize multiple date formats ("08/22/2019" and "08-05-19") Do you know if there will always be one and only one date in the text? Do you know if it will always follow American formatting? If you don't know those things, your problem becomes harder. — Jamie F
– Jamie F, Commented Aug 23, 2019 at 0:53
i know that the format is always mm-dd-yy but the date is always different. it could be 08-22-19 or 07-29-19. Sorry in my example i used / instead of - — PCMike
– PCMike, Commented Aug 23, 2019 at 0:58
@ChetanRanpariya — it's unlikely that the date is at the same offset in each row; the blah blah part of the data is probably of variable length. — Jonathan Leffler
– Jonathan Leffler, Commented Aug 23, 2019 at 1:08
Which DBMS are you using? You probably need to look hard at the 'regular expression' functions it provides, to decide which one(s) will help you. You're trying to split the string into 4 bits, it appears: 'junk-1', 'date', 'commentary', 'junk-2'. And you'd like to capture 'date' and 'commentary' to be added to another table. Creating structure out of unstructured data is hard work. How can you tell the boundary between 'commentary' and 'junk-2'? — Jonathan Leffler
– Jonathan Leffler, Commented Aug 23, 2019 at 1:17

Jervs · Accepted Answer · 2019-08-23 01:42:22Z

1

It's not clear how the MEMO is structured. If the length is fixed 08-05-19 and AUDIT TRANSFERRED FROM 073 TO 057, you can try below query:

DECLARE @sqlstring as varchar(max) = 'the day that i did this 08-05-19 - AUDIT TRANSFERRED FROM 073 TO 057 because I wanted to.'

select 
    substring(@sqlstring, 
        patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%', 
                @sqlstring), 8),

    substring(@sqlstring, 
        (patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%', 
                @sqlstring))+11, 34)

edited Aug 23, 2019 at 1:42

answered Aug 23, 2019 at 1:30

Jervs

3441 silver badge12 bronze badges

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

3 Comments

PCMike Over a year ago

that got me closer Jervs. I didn't think going down that road. The issue I just seen is that now I see some entry's that are like mm-dd-yy blah blah blah mm-dd-yy Audit Transferred from....." so that first date entry is messing it up for me. there are over 500,000 rows in this table.

Jervs Over a year ago

let's find a solution to that first date entry! :D my concern is the transferred from..... how would you know the last word you'll get!

PCMike Over a year ago

You got me close enough that I got it figured out. patindex('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9] - Audit Transferred fixed it

Collectives™ on Stack Overflow

pulling part of a string from SQL database column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related