question

newbee avatar image
0 Votes"
newbee asked VaibhavChaudhari answered

How to extract date from a long string

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

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)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered VaibhavChaudhari edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.