question

sakath2009 avatar image
sakath2009 asked

Retrive number from a string

i want to retrive the 5 digit number starting with 2 in the string. Below are few examples XYZ Reports Earnings Results for the Year 2014; Provides Financial Guidance for the Year **20015** XYZ Reports Earnings Results for the Six Months Ended June 30, 2016; Provides Earnings Guidance for the Full Year of **20016** XYZ Reports Unaudited Consolidated Earnings Results for the First Quarter Ended March 31, 2015; Provides Earnings Guidance for the Year **20105** the 5 digit number can be in the middle of the string also.
string
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
You could try something like this: DECLARE @t TABLE (string VARCHAR(100)); INSERT INTO @t VALUES ('reports year 2014 guidance year 20015'), ('reports 2016 guidance 2016'), ('reports 2017 guidance 20107'), ('reports 20108 guide 2018'), ('contract number 123456789'); SELECT string, CASE WHEN PATINDEX('%2[0-9][0-9][0-9][0-9]%', string) > 0 AND PATINDEX('%2[0-9][0-9][0-9][0-9][0-9]%', string) = 0 -- five digits only THEN SUBSTRING(string, PATINDEX('%2[0-9][0-9][0-9][0-9]%', string), 5) ELSE NULL END AS [5digityear] FROM @t;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.