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.
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;
17 People are following this question.
