<p>I have a column of data that contains a percentage range as a string that I'd like to convert to a number so I can do easy comparisons.</p>

<p>Possible values in the string:</p>

<pre><code>'&lt;5%'
'5-10%'
'10-15%'
...
'95-100%'
</code></pre>

<p>I'd like to convert this in my select where clause to just the first number, 5, 10, 15, etc. so that I can compare that value to a passed in "at least this" value.</p>

<p>I've tried a bunch of variations on substring, charindex, convert, and replace, but I still can't seem to get something that works in all combinations.</p>

<p>Any ideas?</p>

[{'answer_id': 18437, 'author': 'Shazburg', 'author_id': 2165, 'author_profile': 'https://Stackoverflow.com/users/2165', 'pm_score': 0, 'selected': False, 'text': "<p>You can convert char data to other types of char (convert char(10) to varchar(10)), but you won't be able to convert character data to integer data from within SQL.</p>\n"}, {'answer_id': 18451, 'author': 'mercutio', 'author_id': 1951, 'author_profile': 'https://Stackoverflow.com/users/1951', 'pm_score': 0, 'selected': False, 'text': '<p>I don\'t know if this works in SQL Server, but within MySQL, you can use several tricks to convert character data into numbers. Examples from your sample data:</p>\n\n<pre><code>"&lt;5%"     =&gt; 0\n"5-10%"   =&gt; 5\n"95-100%" =&gt; 95\n</code></pre>\n\n<p>now obviously this fails your first test, but some clever string replacements on the start of the string would be enough to get it working.</p>\n\n<p>One example of converting character data into numbers:</p>\n\n<pre><code>SELECT "5-10%" + 0 AS foo ...\n</code></pre>\n\n<p>Might not work in SQL Server, but future searches may help the odd MySQL user :-D</p>\n'}, {'answer_id': 18454, 'author': 'Kibbee', 'author_id': 1862, 'author_profile': 'https://Stackoverflow.com/users/1862', 'pm_score': 0, 'selected': False, 'text': "<p>You'd probably be much better off changing <code>&lt;5%</code> and <code>5-10%</code> to store 2 values in 2 fields.  Instead of storing <code>&lt;5%</code>, you would store 0, and 5, and instead of <code>5-10%</code>, yould end up with 5 and 10.  You'd end up with 2 columns, one called lowerbound, and one called upperbound, and then just check value <code>&gt;=</code> lowerbound AND value <code>&lt;</code> upperbound.</p>\n"}, {'answer_id': 18471, 'author': 'Martin', 'author_id': 770, 'author_profile': 'https://Stackoverflow.com/users/770', 'pm_score': 4, 'selected': True, 'text': "<p>Try this,</p>\n\n<pre><code>SELECT substring(replace(interest , '&lt;',''), patindex('%[0-9]%',replace(interest , '&lt;','')), patindex('%[^0-9]%',replace(interest, '&lt;',''))-1) FROM table1 \n</code></pre>\n\n<p>Tested at my end and it works, it's only my first try so you might be able to optimise it.</p>\n"}, {'answer_id': 18478, 'author': 'AdamSane', 'author_id': 805, 'author_profile': 'https://Stackoverflow.com/users/805', 'pm_score': 0, 'selected': False, 'text': '<p>You can do this in sql server with a cursor. If you can create a CLR function to pull out number groupings that will help. Its possible in T-SQL, just will be ugly.</p>\n\n<p>Create the cursor to loop over the list.\nFind the first number, If there is only 1 number group in their then return it. Otherwise find the second item grouping.</p>\n\n<p>if there is only 1st item grouping returned and its the first item in the list set it to upper bound.\nif there is only 1st item grouping returned and its the last item in the list set it to lower bound.\nOtherwise set the 1st item grouping to lower, and the 2nd item grouping to upper bound</p>\n\n<p>Just set the resulting values back to a table </p>\n'}, {'answer_id': 18485, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 0, 'selected': False, 'text': '<p>The issue you are having is a symptom of not keeping the data atomic.  In this case it looks purely unintentional (Legacy) but here is a <a href="http://books.google.com/books?id=ZO6MF9Ja1zoC&amp;pg=PA170&amp;lpg=PA170&amp;dq=sql+atomic+data&amp;source=web&amp;ots=WLBCmRzH5G&amp;sig=fjRhYDpCwdQpJEmGxRPfieA1FnY&amp;hl=en&amp;sa=X&amp;oi=book_result&amp;resnum=1&amp;ct=result#PPA172,M1" rel="nofollow noreferrer">link</a> about it. </p>\n\n<p>To design yourself out of this create a range_lookup table:</p>\n\n<pre><code>Create table rangeLookup(\n    rangeID int  -- or rangeCD or not at all\n    ,rangeLabel varchar(50)\n    ,LowValue int--real or whatever\n    ,HighValue int \n)\n</code></pre>\n\n<p>To hack yourself out here some pseudo steps this will be a deeply nested mess.</p>\n\n<pre><code>normalize your input by replacing all your crazy charecters.\n    replace(replace(rangeLabel,"%",""),"&lt;","")\n    --This will entail many nested replace statments.\n\nAdd a CASE and CHARINDEX to look for a space if there is none you have your number\n    else use your substring to take everything before the first " ".\n    -- theses steps are wrapped around the previous step.\n</code></pre>\n'}, {'answer_id': 18503, 'author': 'Geoff', 'author_id': 1097, 'author_profile': 'https://Stackoverflow.com/users/1097', 'pm_score': 2, 'selected': False, 'text': "<p>@Martin: Your solution works.</p>\n\n<p>Here is another I came up with based on inspiration from @mercutio</p>\n\n<pre><code>select cast(replace(replace(replace(interest,'&lt;',''),'%',''),'-','.0') as numeric) test\nfrom table1 where interest is not null\n</code></pre>\n"}, {'answer_id': 18522, 'author': 'Kibbee', 'author_id': 1862, 'author_profile': 'https://Stackoverflow.com/users/1862', 'pm_score': 0, 'selected': False, 'text': "<p>It's complicated, but for the test cases you provided, this works. Just replace @Test with the column you are looking in from your table.</p>\n\n<pre><code>DECLARE @TEST varchar(10)\n\nset @Test =  '&lt;5%'\n--set @Test =  '5-10%'\n--set @Test =  '10-15%'\n--set @Test =  '95-100%'\n\nSelect CASE WHEN \nSubstring(@TEST,1,1) = '&lt;' \nTHEN \n0\nELSE \nCONVERT(integer,SUBSTRING(@TEST,1,CHARINDEX('-',@TEST)-1))\nEND\nAS LowerBound\n,\nCASE WHEN \nSubstring(@TEST,1,1) = '&lt;'\nTHEN\nCONVERT(integer,Substring(@TEST,2,CHARINDEX('%',@TEST)-2))\nELSE\nCONVERT(integer,Substring(@TEST,CHARINDEX('-',@TEST)+1,CHARINDEX('%',@TEST)-CHARINDEX('-',@TEST)-1))\nEND\nAS UpperBound\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/18413', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1097/']