## Find integer at end of a string. There *must* be a better solution ?

 Author Message Thorkil Johansen Valued Member Group: General Forum Members Points: 53 Visits: 64 HiI want to find an integer after a hyphen in a string. If found, then use it. If not, then return -1This is the best I could figure out:`with tmp as ( select x = 'some text-1'union select x = 'some text-123'union select x = 'some text-123.4'union select x = 'some text 123'union select x = 'some text-'union select x = 'some text'union select x = 'some text-xyz')select * ,case when CHARINDEX('-',x) > 0 and LEN(x) > CHARINDEX('-',x) then iif(TRY_CONVERT(int, SUBSTRING(x,1 + CHARINDEX('-',x),LEN(x)-CHARINDEX('-',x))) IS NULL, -1, SUBSTRING(x,1 + CHARINDEX('-',x),LEN(x)-CHARINDEX('-',x)) ) else -1 end as result from tmp` Eugene Elutin SSC-Dedicated Group: General Forum Members Points: 31716 Visits: 5478 `...select *, ISNULL(CAST(SUBSTRING(x,NULLIF(PATINDEX('%-[0-9]%',x),0) + 1,1) AS INT),-1) from tmp` _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help Lowell SSC Guru Group: General Forum Members Points: 182023 Visits: 41569 probably very similar, using reverse and patindex for the first non numeric character: from there, for the 1/-1, a case based on the len() would be what i would use.`with tmp as ( select x = 'some text-1'union select x = 'some text-123'union select x = 'some text-123.4'union select x = 'some text 123'union select x = 'some text-'union select x = 'some text'union select x = 'some text-xyz')select x,REVERSE(SUBSTRING(REVERSE(x),0,PATINDEX('%[^0-9]%',REVERSE(x))))FROM tmp` Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! Lowell SSC Guru Group: General Forum Members Points: 182023 Visits: 41569 doh!, if it ends in [0-9] might be even easier?` SELECT *, CASE WHEN x LIKE '%[0-9]' THEN 1 ELSE -1 END from tmp` Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! Eugene Elutin SSC-Dedicated Group: General Forum Members Points: 31716 Visits: 5478 Lowell (7/25/2013)probably very similar, using reverse and patindex for the first non numeric character: from there, for the 1/-1, a case based on the len() would be what i would use.`with tmp as ( select x = 'some text-1'union select x = 'some text-123'union select x = 'some text-123.4'union select x = 'some text 123'union select x = 'some text-'union select x = 'some text'union select x = 'some text-xyz')select x,REVERSE(SUBSTRING(REVERSE(x),0,PATINDEX('%[^0-9]%',REVERSE(x))))FROM tmp`It returns 4 for "select x = 'some text-123.4'". _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help Eugene Elutin SSC-Dedicated Group: General Forum Members Points: 31716 Visits: 5478 Lowell (7/25/2013)doh!, if it ends in [0-9] might be even easier?` SELECT *, CASE WHEN x LIKE '%[0-9]' THEN 1 ELSE -1 END from tmp`that will return 1 for `union select x = 'some text-321'`I thought OP wanted first digit. _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help ChrisM@home SSChampion Group: General Forum Members Points: 12801 Visits: 10881 thorkil (7/25/2013)HiI want to find an integer after a hyphen in a string. If found, then use it. If not, then return -1What do you want to do with 123.4? Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden Eugene Elutin SSC-Dedicated Group: General Forum Members Points: 31716 Visits: 5478 I've couldn't run OP code in SQL2008 as he is using 2012 function.Now, I can see what is result of his query. The following will produce the same:`select * ,ISNULL(TRY_CONVERT(INT,SUBSTRING(x,NULLIF(PATINDEX('%-[0-9]%',x),0) + 1,LEN(x)) ),-1) from tmp`Looks like 123.4 is not an integer, so -1 should be returned. _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help Lowell SSC Guru Group: General Forum Members Points: 182023 Visits: 41569 Eugene Elutin (7/25/2013)Lowell (7/25/2013)doh!, if it ends in [0-9] might be even easier?` SELECT *, CASE WHEN x LIKE '%[0-9]' THEN 1 ELSE -1 END from tmp`that will return 1 for `union select x = 'some text-321'`I thought OP wanted first digit.i might have misread this, i thought he wanted to return a 1 /-1 flag ,but also definitely misread integer to be is kinda sorta numeric, so i thought that one might hit the sweet spot. Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! Thorkil Johansen Valued Member Group: General Forum Members Points: 53 Visits: 64 Someone said: Looks like 123.4 is not an integer, so -1 should be returned.Yes, that's rightThanx a lot for all the answers... It's evening here in Copenhagen. I will take a closer look tomorrow at work.This is really a nice forum... :-)/T