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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply