• Status: Solved
• Priority: Medium
• Security: Public
• Views: 243

# pull numeric beginning part of a column value, ignore letters, sort on the results

Values of column "acreage":

5
10
20
1 to 5
3 to 8

I want to numeric sort on either the number (in the case of the first three), or the first number (case of last two). So ignore the " to 5" and " to 8", but otherwise it would be convert (int, acreage). How can I do this?

Thank you!
0
• 3
• 2
• 2
2 Solutions

Commented:
declare @x varchar(max)
set @x = '3'

select left(@x, case when charindex(' ', @x) = 0 then len(@x) else charindex(' ', @x) end)

so, for a table:

select left(fieldname, case when charindex(' ', fieldname) = 0 then len(fieldname) else charindex(' ', fieldname) end)
from table
0

Commented:
then just throw your cast around that value:

select cast(left(fieldname, case when charindex(' ', fieldname) = 0 then len(fieldname) else charindex(' ', fieldname) end) as int)
from table
0

Commented:
Assuming your column *always* starts with at least one numeral...

SELECT *
FROM SomeTable
ORDER BY CAST(LEFT(LEFT(acreage, PATINDEX('%[^0-9]%', acreage) - 1), 3) AS int)
0

Web DeveloperAuthor Commented:
chapmandew: I'm trying to put this in an "order by" claus, not a select.

matthewspatrick: I get this error: "Invalid length parameter passed to the SUBSTRING function"

The column always starts with at least one numeral. Sometimes there are other characters (i.e., spaces and/or letters) after the numeral (which can be any number of characters in length), sometimes not.
0

Commented:
select *
from table
order by cast(left(fieldname, case when charindex(' ', fieldname) = 0 then len(fieldname) else charindex(' ', fieldname) end) as int)
0

Commented:
SELECT *
FROM SomeTable
ORDER BY CAST(LEFT(LEFT(acreage, CASE WHEN PATINDEX('%[^0-9]%', acreage) > 0 THEN (PATINDEX('%[^0-9]%', acreage) - 1) ELSE LEN(acreage) END), 3) AS int)
0

Web DeveloperAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.