quote:

---

Originally posted by ehorn

create table test (value varchar(20))

insert into test 
select 'Stylus 50' union all
select 'Stylus 6000' union all
select 'Stylus 73'

select *
from test
order by convert(int,substring(value,charindex(' ',value,1),len(value)))

drop table test

---

quote:

---

Originally posted by ehorn

create table test (value varchar(20))

insert into test 
select 'Stylus 50' union all
select 'Stylus 6000' union all
select 'Stylus 73'

select *
from test
order by convert(int,substring(value,charindex(' ',value,1),len(value)))

drop table test

---

quote:

---

Originally posted by ehorn

There are many ways to perform the order by. This particluar solution is based on your posted values. It trims the numbers off of the end, then converts them to integers. If you know your domain of results and desired sorting order, a solution can be derived. You will have to test this particlar solution to see if it will fit your needs. If it does not, you can provide a set of data that is more representative of your output and someone here can work up an order by statement to meet your needs.

---

quote:

---

Originally posted by derrickleggett

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnGENRemoveNonNumeric]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnGENRemoveNonNumeric]
GO

CREATE FUNCTION dbo.fnGENRemoveNonNumeric
--Name: fnGENRemoveNonNumeric
--Purpose: This function strips all non-numeric fields from a string.
--
--Format: dbo.fnGENRemoveNonNumeric('field')
--Example_1: SELECT dbo.fnGENRemoveNonNumeric(Address) FROM Customers
--Example_2: SELECT Address FROM Customers ORDER BY dbo.fnGENRemoveNonNumeric(Address)
--
--Action: Author: Date: Comments:
--------- ---------- --/--/---- ----------------------------------------
--Created Derrick Leggett 08/11/2003 Initial Development
--
(
@inputText VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN

--Declare needed values.
DECLARE
@prepText VARCHAR(255),
@i INT,
@char CHAR(1)

--Populate values to control the looping validation of text.
SELECT
@prepText = '',
@i = 1

--Loop through text to identify valid characters.
WHILE @i <= LEN(@inputText)
BEGIN

--Select the value of the string in position @i.
SELECT @char = (SELECT SUBSTRING(@inputText, @i, 1))

--Add next value to string. Can be 0-9.
IF CHARINDEX(@char, '0123456789') <> 0
SET @prepText = @prepText + @char

--Increment the string search to go to the next character.
SELECT @i = @i + 1
END

RETURN @prepText

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



declare @values table(
value varchar(55))

insert @values(value)values('StylusLS')
insert @values(value)values('Stylus 50')
insert @values(value)values('Stylus 6000')
insert @values(value)values('Stylus 73')
insert @values(value)values('Too poor to buy a printer')
insert @values(value)values('Stylus')

select value from @values

select value from @values order by value + dbo.fnGENRemoveNonNumeric(value)


Would be careful how I use this. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

---

quote:

---

Originally posted by ehorn

select * 
from test
order by 
	replace(value,reverse(left(reverse(value),charindex(' ',reverse(value),1))),''),
	convert(int,ltrim(reverse(left(reverse(value),charindex(' ',reverse(value),1)))))

---

create table test (value varchar(20))

insert into test 
select 'Fax Color 32' union all
select 'Stylus 3' union all
select 'Stylus Color 2' union all
select 'Stylus Color 400' union all
select 'Stylus 100' union all
select 'Stylus Color' union all
select 'Stylus' union all
select 'fax123'

select value 
from
(
	select * 
	, case when isnumeric(left(reverse(value),charindex(' ',reverse(value),1))) = 1 
		then replace(value,reverse(left(reverse(value),charindex(' ',reverse(value),1))),'') 
		else value 
		end value_text

	, case when isnumeric(left(reverse(value),charindex(' ',reverse(value),1))) = 1 
		then  reverse(left(reverse(value),charindex(' ',reverse(value),1))) 
		else 0 
		end num

	from test
) d
order by value_text, num

drop table test

declare @test table (value varchar(20))

insert into @test 
select 'Fax Color 32' union all select 'Stylus 3' union all
select 'Stylus Color 2' union all select 'Stylus Color 400' union all
select 'Stylus 100' union all select 'Stylus Color' union all
select 'Stylus' union all select 'fax123'

select value, SUBSTRING(value + '00000000000000000', 1, 20) AS NewValue
from @test
order by NewValue

--create tally table for the function

create table numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255
begin
	insert into numbers
	select @n
	set @n = @n + 1
end
GO

create function dbo.fn_remove_non_numeric (@value varchar(255))
returns varchar(255)
as
begin

declare @chars table (vc char(1), list varchar(255))

insert into @chars (vc)
select substring(@value,n,1)
from numbers
where n <=len(@value)
and substring(@value,n,1) like '[0-9]'

declare @list varchar(255)
select @list = ''

update @chars
set @list = list = @list + vc

return 
(
	select max(list)
	from @chars
)
end
GO

declare @value varchar(255)
select @value = 'Some 111 value with num222eric 333data in 444 it'
select dbo.fn_remove_non_numeric(@value) 

drop function dbo.fn_remove_non_numeric

quote:

---

Originally posted by jsmith8858

"Model number" should be a seperate column in your table. never combine different pieces of information into 1 column in your data design ... you can always concatenate different columns together very easily, and sort by the different columns, but it is not always possible or desirable to try to parse a column like this out into multiple values.

- Jeff

---

quote:

---

Originally posted by Lumbago

What about this:

declare @test table (value varchar(20))

insert into @test 
select 'Fax Color 32' union all select 'Stylus 3' union all
select 'Stylus Color 2' union all select 'Stylus Color 400' union all
select 'Stylus 100' union all select 'Stylus Color' union all
select 'Stylus' union all select 'fax123'

select value, SUBSTRING(value + '00000000000000000', 1, 20) AS NewValue
from @test
order by NewValue

---

quote:

---

Originally posted by ehorn

Derrick, here is a set-based method for the function you provided:

--create tally table for the function

create table numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255
begin
	insert into numbers
	select @n
	set @n = @n + 1
end
GO

create function dbo.fn_remove_non_numeric (@value varchar(255))
returns varchar(255)
as
begin

declare @chars table (vc char(1), list varchar(255))

insert into @chars (vc)
select substring(@value,n,1)
from numbers
where n <=len(@value)
and substring(@value,n,1) like '[0-9]'

declare @list varchar(255)
select @list = ''

update @chars
set @list = list = @list + vc

return 
(
	select max(list)
	from @chars
)
end
GO

declare @value varchar(255)
select @value = 'Some 111 value with num222eric 333data in 444 it'
select dbo.fn_remove_non_numeric(@value) 

drop function dbo.fn_remove_non_numeric

---

quote:

---

Lumbago,
Thanks for giving it a try, but I still keep getting the same results.

Jose

---

quote:

---

Originally posted by ehorn

Jose,

Sorry for the confusion, the function I provided was not intended for your situation. Did you try the derived table solution I psted earlier in this post??

---