You must Sign In to post a response.
  • Category: SQL Server

    AlphaNumeric with hyphen sorting

    Please find my ID here. I would like to sort this ID either in SQL or in Crystal Report formula. Thank You

    ID

    P1
    P2
    P3
    P4-1
    P4-2
    P4-3
    P5
    P6
    P7-a
    P7-b
    P7-c
    P8
    P9
    .
    .
    .
    P20
  • #746927
    Refer the below scrip

    create table #test (id nvarchar(25))

    select * from #test

    insert into #test
    select 'P1' union all
    select 'P2' union all
    select 'P3' union all
    select 'P4-1' union all
    select 'P4-2' union all
    select 'P4-3' union all
    select 'P5' union all
    select 'P6' union all
    select 'P7-a' union all
    select 'P7-b' union all
    select 'P7-c' union all
    select 'P8' union all
    select 'P9' union all
    select 'P20'

    select * from #test
    order by cast(SUBSTRING(id, 1,
    case when patindex('%[^0-9]%',id) > 0 then patindex('%[^0-9]%',id)- 1 else LEN(id) end) as int), id

    Select id From #test
    ORDER BY
    Case When IsNumeric(id) = 1 then Right(Replicate('0',21) + id, 20)
    When IsNumeric(id) = 0 then Left(id + Replicate('',21), 20)
    Else id
    End

    Regards
    Siva

  • #746933
    Hi Siva,

    Thank you for your reply. When I execute the script it came as below

    P1
    P2
    P20
    P3
    P4

    and so on..

    P20 supposed to come at last. How can I do that?

  • #746934
    Hi

    By using this function we can sort AlphaNumeric data.


    create FUNCTION [dbo].[AlphaNum]
    (
    @input varchar(max)
    )
    RETURNS varchar(max)
    AS
    BEGIN
    declare @num varchar(50)
    declare @space varchar(50)
    declare @index int = 1

    set @num = LEFT(SUBSTRING(@input, PATINDEX('%[0-9.-]%', @input), 8000), PATINDEX('%[^0-9.-]%'
    ,SUBSTRING(@input, PATINDEX('%[0-9.-]%', @input), 8000) + 'X')-1)
    set @space = replicate(' ', 20 - len(@num))

    return replace(@input, @num, @space + @num)
    END

  • #747210
    Thank you all. I tried everything, but still not working. Could you please send an other option.

    Thank you


  • Sign In to post your comments