How can I convert or cast a string containing some alpha and commas to an integer

Question

Post reply

How can I convert or cast a string containing some alpha and commas to an integer

Tom Currier

Old Hand

Points: 375
More actions
March 14, 2005 at 11:25 am

#66587

I need to convert a string that contains values like the following:
80 beds {could be any text in any position}
1,504
8000
to an Integer

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Use PatIndex() to look for start of numerics, and subsequent start of non numerics.

Select PatIndex('%[0-9]%', YourText) -- Get the 1st numeric character

Select PatIndex('%[^0-9]%', SubString(YourText, PatIndex('%[0-9]%', YourText) + 1, 999 ) -- Get the 1st non-numeric character after the 1st number

Only problem will be the numbers with embedded commas - maybe use Replace() to get rid of those 1st, before applying the PatIndex()'s

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 2

You could also hunt this site for scripts that people have posted that essentially duplicate the VB Split function and return a one-column-wide table of each word in the string. Then you could select from the table where PatIndex('%[0-9]%', OneColumn) > 0 to get the numeric value.

There is no "i" in team, but idiot has two.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

To tell the truth, I would be afraid to do such conversion ... it seems that you can't rely much on what is written in the column. What is 1,504? Is it (approximately) one and half, or one thousand five hundred? Can you be sure that the comma always means the same? What if there is entry like "80 beds model 121" or "model 121 beds, 80 pcs"? What is someone writes ten thousand six hundred like 10 600, while in another row the same has a different meaning, e.g. 600 being part of description and only 10 is amount?

Well, you should know your DB better than me :-)) maybe I'm overacting, but it really pays to be careful in case of such data manipulation.

Good luck!

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

"Well, you should know your DB better than me :-)) maybe I'm overacting, but it really pays to be careful in case of such data manipulation."

No, you're not overacting (or overreacting , I very much guess he's never had to deal with data like this, otherwise he wouldn't be posting here...he's be running to the nearest bar to drown his sorrows.

Tread carefully! Make sure people understand that the quality of the output is questionable at best; and could blow up your code at worse. Make sure that you use isnumeric(@Text) to check the validness of integers before trying to insert them into an integer field. If you have to deal with decimal numbers you're in for even more of a world of hurt...using float datatypes would be only way (and that may cause rounding errors).

Really, all you can say with this is, "Garbage in, garbage out." The place for data validation is when the data is captured, and not later down the line.

cl

Signature is NULL