Find number in String and Return INT

Question

Post reply

Find number in String and Return INT

ringovski

Hall of Fame

Points: 3521
More actions
August 8, 2013 at 12:33 am

#275961

Hi All,
I have a series of strings in a variable @buyer
3, RRRRR
4, SSSSS
5, DDDDDDDDDDD
6, AAAA
7, PPPPPP
8, UUUUUU
9, MMMMMM
10, LLLLLL
11, IIIII
12, EEEEE
and I only want to sub string the number then convert it to an INT, so i can use it in a where clause.
select *
from company
where company_id = CAST(SUBSTRING(@buyer,1,LEN(@buyer) - CHARINDEX(',',@buyer,0)) AS INT)
order by company_id
substring (@buyer, start at position 1,find length of @buyer minus the index position of the comma in @buyer) then cast int. but it doesn't work. say conversion failed when converting value 3,RRRRR.
I think it should be pretty simple, but I can't see it.
Thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

Try this:

DECLARE @buyer VARCHAR(10) = '12, RRRRR';

SELECT CAST(SUBSTRING(@buyer,1,CHARINDEX(',',@buyer,0) - 1) AS INT)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ringovski Hall of Fame Points: 3521 More actions · Answer 2

ringovski

Hall of Fame

Points: 3521

August 8, 2013 at 5:26 pm

#1640023

Ah perfect Koen thanks heaps.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

You might want to allow for no comma being in the string (just in case).

And you can remove some unnecessary elements from the code:

SELECT CAST(LEFT(@buyer, CHARINDEX(',', @buyer + ',') - 1) AS INT)

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Dwain Camps SSC Guru Points: 86913 More actions · Answer 4

Many ways to skin this proverbial cat:

WITH Buyers (buyer) AS (

SELECT '3, RRRRR'

UNION ALL SELECT '4, SSSSS'

UNION ALL SELECT '5, DDDDDDDDDDD'

UNION ALL SELECT '6, AAAA'

UNION ALL SELECT '7, PPPPPP'

UNION ALL SELECT '8, UUUUUU'

UNION ALL SELECT '9, MMMMMM'

UNION ALL SELECT '10, LLLLLL'

UNION ALL SELECT '11, IIIII'

UNION ALL SELECT '12, EEEEE'

)

SELECT 0+STUFF(buyer, PATINDEX('%[^0-9]%', buyer), 8000, '')

FROM Buyers

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St