Hi All,
have a long string like below in my column and I need to extract only the date (2021-07-05) from it.
Could any one please help
Name - Koteswararao vp Department - Prod Location - Hyderabad Domain - ND Job Title - Technical-ABAP Date of Hire - 2021-07-05 Vendor ID - v2345 Candidate ID - Associate ID - 3334 Name - Hardware Allocation
This can be a bit of a pita. It helps greatly if there is one and only one acceptable date format, like YYYY-MM-DD (padded with zeros). Another issue is what to do if there are 2 dates in the string? To look for occurrences of more than 1 date within the string you could reverse the string (and criteria) and compare PATINDEX locations. I'm not aware of programmatic way to do this so I type out the search condition 2 ways. Assuming YYYY-MM-DD and rows with more than 1 date within the string are excluded you could try something like this. To see the excluded row the query could be executed without the WHERE clause
declare
@str table(long_str varchar(4000) not null);
insert @str(long_str) values
('Name - Koteswararao vp Department - Prod Location - Hyderabad Domain - ND Job Title - Technical-ABAP Date of Hire - 2021-07-05 Vendor ID - v2345 Candidate ID - Associate ID - 3334 Name - Hardware Allocation'),
('Name - Koteswararao vp Department - Prod Location - Hyderabad Domain - ND Job Title - Technical-ABAP Date of Hire - 2021-07-05 Vendor ID - v2345 Candidate ID - Associate ID - 3334 Name - Hardware 2019-12-31 Allocation');
declare
@srch varchar(100)='%[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]%',
@srch_rv varchar(100)='%[0-9][0-3]-[0-9][0-1]-[0-9][0-9][0-9][1-2]%';
/* Assumes YYYY-MM-DD */
select try_cast(substring(long_str, v.px_loc, 10) as date) found_dt,
v.px_loc found_start,
v.str_len string_len,
v.px_loc_rv found_start_rv
from @str
cross apply (values (patindex(@srch, long_str),
len(long_str),
patindex(@srch_rv, reverse(long_str)))) v(px_loc, str_len, px_loc_rv)
where v.px_loc=v.str_len-v.px_loc_rv-10+2;
found_dtfound_startstring_lenfound_start_rv
2021-07-0511720681
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 23, 2021 at 1:30 pm
Maybe it's better to nest PATINDEX functions instead of reversing the string(s)
declare
@srch varchar(100)='%[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]%';
/* Assumes YYYY-MM-DD */
select try_cast(substring(long_str, v.px_loc, 10) as date) found_dt,
v.px_loc found_start,
v.str_len string_len,
dt.px_loc found_start_2nd
from @str
cross apply (values (patindex(@srch, long_str),
len(long_str))) v(px_loc, str_len)
cross apply (values (patindex(@srch, substring(long_str,
v.px_loc+10, v.str_len-v.px_loc-10)))) dt(px_loc)
where dt.px_loc=0;
found_dtfound_startstring_lenfound_start_2nd
2021-07-051172060
Without the where clause
found_dtfound_startstring_lenfound_start_2nd
2021-07-051172060
2021-07-0511721771
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 11, 2021 at 7:10 pm
Hi,
Since all the dates have the wording 'Date of Hire' in front of the date, look for that.
Quick example as below.
declare @string varchar(150)
set @string = 'xxxxxxxx - Date of Hire - 2021-07-05 yyyuusdfdsf - '
----select len('Date of Hire - 2021-07-05') -25
select replace(SUBSTRING(@string, charindex('Date of Hire', @String,0),25),'Date Of Hire - ','')
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy