Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Find integer at end of a string. There *must* be a better solution ? Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:48 AM
Points: 11, Visits: 49
Hi

I want to find an integer after a hyphen in a string. If found, then use it. If not, then return -1

This 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

Post #1477465
Posted Thursday, July 25, 2013 7:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
...

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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1477475
Posted Thursday, July 25, 2013 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477506
Posted Thursday, July 25, 2013 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477509
Posted Thursday, July 25, 2013 8:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1477511
Posted Thursday, July 25, 2013 8:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1477513
Posted Thursday, July 25, 2013 8:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 1,059, Visits: 5,758
thorkil (7/25/2013)
Hi

I want to find an integer after a hyphen in a string. If found, then use it. If not, then return -1



What 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
Post #1477521
Posted Thursday, July 25, 2013 9:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1477577
Posted Thursday, July 25, 2013 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477580
Posted Thursday, July 25, 2013 9:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:48 AM
Points: 11, Visits: 49

Someone said: Looks like 123.4 is not an integer, so -1 should be returned.

Yes, that's right

Thanx 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
Post #1477589
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse