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

JAYRAM

13y1.4kSQL

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hanook13y ago
Accepted 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 <= LEN(@Find) BEGIN

SELECT @Input = REPLACE(@Input, SUBSTRING(@Find, @i, 1), SUBSTRING(@Replace, @i, 1))
SELECT @i = @i + 1

END
RETURN @Input
END
GO
0
Hanook13y ago
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
0
JAYRAM13y ago
it shows error like .....below

Conversion failed when converting the varchar value '300, RJVGNHI NGR' to data type int.
0
JAYRAM13y ago
thanks for replay........

quarie not working...i mean not shown as above format..............
0
Hanook13y ago
Hi Ani,

Simply you can query like this
SELECT * FROM TableName
ORDER BY houseno
0
Hanook13y ago
ORDER BY CAST( SUBSTRING(REVERSE(houseno ), PATINDEX('%-%', REVERSE(houseno))-1, 1) AS INT)
0