none
Help in Sort of Name with the numeric number

    Question

  • Hello, I want to sort the NameWithDate colum with the numeric values which are at the end. How I can sort with the numeric numbers.

    Select NameWithDate from #data

    Dobra,Sim-02

    Ahmed,S-04

    Suresh,John-01

    Khan,K-03

    Expected result

    ------------------

    NameWithDate 

    Suresh,John-01

    Dobra,Sim-02

    Khan,K-03

    Ahmed,S-04

    Wednesday, January 3, 2018 4:03 PM

Answers

  • select NameWithDate from #data order by isnull(try_cast(right(NameWithDate,2) as int),0)

    • Edited by SQLNeophyte Wednesday, January 3, 2018 4:41 PM
    • Marked as answer by Kenny_Gua Wednesday, January 3, 2018 5:28 PM
    Wednesday, January 3, 2018 4:40 PM
  • Try:

    SELECT
    	T1.col1,
    	TRY_CAST(REVERSE(LEFT(T2.col1, CHARINDEX('-', T2.col1) - 1)) AS int) AS col2
    FROM
    	(
    	VALUES
    		('Dobra,Sim-02'),
    		('Ahmed,S-04'),
    		('Suresh,John-01'),
    		('Khan,K-03'),
    		('Jon,Marie-Andrée-25')
    	) AS T1(col1)
    	CROSS APPLY
            (
    	VALUES (REVERSE(T1.col1))
    	) AS T2(col1)
    ORDER BY
    	col2;
    Let us know if there are other extreme cases (e.g. no row with dash as part of the column's value).


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    Wednesday, January 3, 2018 4:57 PM
    Moderator
  • Hi,

    I am getting the following error message when I added the following Name data. There are two '-' sign in last name. Two '-' sign can also be used in first name.

    Jon,Marie-Andrée-25


    Error:Conversion failed when converting the nvarchar value 'Andrée-25' to data type int.


    you can try this then

    hope number of - wont go beyond 3

    DECLARE @t table
    (
    NameWithDate varchar(100)
    )
    
    INSERT @t
    VALUES ('Dobra,Sim-02'),
    ('Name,WO Number'),
    ('Ahmed,S-04'),
    ('Suresh,John-01'),
    ('Khan,K-03'),
    ('Jon,Marie-Andrée-25'),
    ('Jon,Marie-Andrée-Peter-19')
    
    SELECT *
    FROM @t
    ORDER BY CAST(REPLACE(PARSENAME(REPLACE(NameWithDate,'-','.'),1),NameWithDate,'') AS int)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Kenny_Gua Wednesday, January 3, 2018 5:27 PM
    Wednesday, January 3, 2018 5:09 PM

All replies

  • Select NameWithDate from #data

    Order by Cast(Reverse(Left(Reverse(NameWithDate),2)) as int)

    Wednesday, January 3, 2018 4:12 PM
    Moderator
  • Try:

    SELECT
    	col1
    FROM
    	(
    	VALUES
    		('Dobra,Sim-02'),
    		('Ahmed,S-04'),
    		('Suresh,John-01'),
    		('Khan,K-03')
    	) AS T(col1)
    ORDER BY
    	CAST(STUFF(col1, 1, CHARINDEX('-', col1), '') AS int);


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    Wednesday, January 3, 2018 4:15 PM
    Moderator
  • DECLARE @t table
    (
    NameWithDate varchar(100)
    )
    
    INSERT @t
    VALUES ('Dobra,Sim-02'),
    ('Name,WO Number'),
    ('Ahmed,S-04'),
    ('Suresh,John-01'),
    ('Khan,K-03')
    
    SELECT *
    FROM @t
    ORDER BY CAST(STUFF(NameWithDate,1,CHARINDEX('-',NameWithDate + '-'),'') AS int)
    
    
    /*
    output
    -------------------------------------------------
    NameWithDate
    -------------------------------------------------
    Name,WO Number
    Suresh,John-01
    Dobra,Sim-02
    Khan,K-03
    Ahmed,S-04
    
    
    */



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Wednesday, January 3, 2018 4:24 PM
    Wednesday, January 3, 2018 4:21 PM
  • Hi,

    I am getting the following error message when I added the following Name data. There are two '-' sign in last name. Two '-' sign can also be used in first name.

    Jon,Marie-Andrée-25


    Error:Conversion failed when converting the nvarchar value 'Andrée-25' to data type int.


    • Edited by Kenny_Gua Wednesday, January 3, 2018 4:39 PM
    Wednesday, January 3, 2018 4:38 PM
  • select NameWithDate from #data order by isnull(try_cast(right(NameWithDate,2) as int),0)

    • Edited by SQLNeophyte Wednesday, January 3, 2018 4:41 PM
    • Marked as answer by Kenny_Gua Wednesday, January 3, 2018 5:28 PM
    Wednesday, January 3, 2018 4:40 PM
  • Try:

    SELECT
    	T1.col1,
    	TRY_CAST(REVERSE(LEFT(T2.col1, CHARINDEX('-', T2.col1) - 1)) AS int) AS col2
    FROM
    	(
    	VALUES
    		('Dobra,Sim-02'),
    		('Ahmed,S-04'),
    		('Suresh,John-01'),
    		('Khan,K-03'),
    		('Jon,Marie-Andrée-25')
    	) AS T1(col1)
    	CROSS APPLY
            (
    	VALUES (REVERSE(T1.col1))
    	) AS T2(col1)
    ORDER BY
    	col2;
    Let us know if there are other extreme cases (e.g. no row with dash as part of the column's value).


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    Wednesday, January 3, 2018 4:57 PM
    Moderator
  • Hi,

    I am getting the following error message when I added the following Name data. There are two '-' sign in last name. Two '-' sign can also be used in first name.

    Jon,Marie-Andrée-25


    Error:Conversion failed when converting the nvarchar value 'Andrée-25' to data type int.


    you can try this then

    hope number of - wont go beyond 3

    DECLARE @t table
    (
    NameWithDate varchar(100)
    )
    
    INSERT @t
    VALUES ('Dobra,Sim-02'),
    ('Name,WO Number'),
    ('Ahmed,S-04'),
    ('Suresh,John-01'),
    ('Khan,K-03'),
    ('Jon,Marie-Andrée-25'),
    ('Jon,Marie-Andrée-Peter-19')
    
    SELECT *
    FROM @t
    ORDER BY CAST(REPLACE(PARSENAME(REPLACE(NameWithDate,'-','.'),1),NameWithDate,'') AS int)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Kenny_Gua Wednesday, January 3, 2018 5:27 PM
    Wednesday, January 3, 2018 5:09 PM
  • Thanks All
    Wednesday, January 3, 2018 5:29 PM
  • I wish you would please read at least one book RDBMS before you post again. We don't do this. Look at the normal forms and Codd's 12 rules. A column is by definition a scaler value. You're shoving two totally different things into the same column! Please learn what a scaler value is.

    It would also be nice if you would be polite enough to at least try to post DDL. Did you read the forum rules?

    CREATE TABLE Foobar
    (foo_name VARCHAR (15) NOT NULL PRIMARY KEY,
     foo_date DATE NOT NULL);

    The first thing we have to do is separate out your denormalized data into its two scaler components. Then we have to find out which one or more of these columns is the key. Again, if you read that book on RDBMS, you will find that by definition, a table must have a key.

    However when we go to split out the denormalized data, you describe a date that your data does not have a date in it! It seems to end with strings of digits. The only format allowed in ANSI/ISO standard SQL is based on the ISO 8601 display format for dates, which is"yyyy-mm-dd" and not a string of digits in the format you used.

    Here is totally unsupported guess at what you might have meant:

    INSERT INTO Foobar
    VALUES
    ('Dobra,Sim' '2018-01-02'),
    ('Ahmed,S', '2018-01-04')
    ('Suresh,John', '2018-01-01'),
    ('Khan,K', '2018-01-03');

    Not only did you fail to post an insertion statement, but what you're trying to do makes no sense. SQL is based on sets, not sequential files. By its nature, a table in SQL has no ordering! This is usually explained in the first or second chapter of any book on the language. If you want to see something sorted you have to create a cursor (implicit with an ORDER BY clause or an explicit CREATE CURSOR).

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, January 4, 2018 12:10 AM