display order by like 12345...............quarie.

Question

Examples of values I want to put in order

houseno
3-13-1
3-13-3/a
3-13-3/b
3-13-2/a
3-13-4
3-13-6
3-13-5/a
3-13-4/c

i want output like this below

houseno
3-13-1
3-13-2/a
3-13-3/a
3-13-3/b
3-13-4
3-13-4/c
3-13-5/a
3-13-6

Hanook · Answer

First create function ( dbo.Thicky_Pants ) for traslate functionality.. and try the follwoing query SELECT * FROM TableName ORDER BY CAST( dbo.Thicky_Pants ( RIGHT(houseno PATINDEX('%-%' REVERSE(houseno))-1)  '[abcde/]' '') AS INT) CREATE FUNCTION dbo.Thicky_Pants ( @Input AS VarChar(1000) @Find AS VarChar(100) @Replace AS VarChar(100) ) RETURNS VarChar(1000) AS BEGIN DECLARE @i AS TinyInt SELECT @i = 1 WHILE @i SELECT @Input = REPLACE(@Input SUBSTRING(@Find @i 1) SUBSTRING(@Replace @i 1)) SELECT @i = @i + 1 END RETURN @Input END GO

Hanook · Answer

Hi It dependa on the input data.. can you tell me the format/sample data of houseno column... In houseno column do you have complete address (like 300 RJVGNHI NGR) ? Post the result of the follwoing query: SELECT dbo.Thicky_Pants ( RIGHT(houseno PATINDEX('%-%' REVERSE(houseno))-1)  ' [abcdefghijklmnopqrstuvxyz0123456789/] ' '') FROM TableName

JAYRAM · Answer

it shows error like .....below Conversion failed when converting the varchar value '300 RJVGNHI NGR' to data type int.

JAYRAM · Answer

thanks for replay........ quarie not working...i mean not shown as above format..............

Hanook · Answer

Hi Ani Simply you can query like this SELECT * FROM TableName ORDER BY houseno

Hanook · Answer

ORDER BY CAST( SUBSTRING(REVERSE(houseno ) PATINDEX('%-%' REVERSE(houseno))-1 1) AS INT)

display order by like 1,2,3,4,5...............quarie.