Clever Geek Handbook

Sort by name but ignore quotes?

Do I need to sort the column by name and ignore the quotes that are some elements?

For example:

"That's cool"

Another article

Article 2

Hello

Will sort as:

Another article

Article 2

Hello

"That's cool"

The quotes are already in the database ...

+2


source to share


5 answers 


SELECT * FROM yourTable ORDER BY TRIM(BOTH '"' FROM title);


+2


source


Depends on which language you are using. In MS SQL, I am using REPLACE command. So if I have a table, users, with a column name that I am sorting, I do this, which removes all double quotes from the string:



SELECT     *
FROM         dbo.Users
ORDER BY REPLACE(firstName, '"', '')
+5


source


A trick that may be acceptable, and faster than a TRIM () or REPLACE () based solution, is to focus only on the first character that is a quote (or by extension, a non-alphanumeric character).

ORDER BY CASE LEFT(myCol, 1) 
      WHEN '"' THEN REPLACE(myCol, '"', '')
      ELSE myCol
    END CASE

In general, for a larger dataset, this kind of processing should be done at load time, for example adding columns for queries rather than columns for display purposes.

+3


source 


SELECT *
 FROM table
ORDER BY CASE WHEN len(col) >= 2
              THEN REPLACE(LEFT(COL, 1), '"', '') 
                 + RIGHT(LEFT(col, LEN(col)-1), LEN(LEFT(col, LEN(col)-1))-1) 
                 + REPLACE(RIGHT(col, 1), '"', '')
              ELSE col
         END

in ORDER BY

, I exclude the double quotes by replacing it 'inside' the 1st and last characters with '. Of course, it is only valid when col length> = 2.

+1


source 


SELECT name
FROM myTable
ORDER BY replace(name, '"', '')
0


source






More articles:


All Articles