How to extract date from a long string

Question

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

Answer 1

Is it always the same ODBC format yyyy-MM-dd? Then this should work:

declare @string varchar(200) = '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';

select substring(@string, PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @string), 10)

Answer 2

If you want to get the date followed by Date of Hire string then use below but note that we are hardcoding things here.

declare @string varchar(200) = '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';

 select substring(@string, CHARINDEX('Date of Hire - ',@string)+15,10)

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav