Sort Results ignoring symbols like ! ? * "

[AlexeiXX3]

Hi, thanks for looking I need to sort the results of my query, lets say i have the following values in my Articles table Id, Title -------------------------- 1, "The less you talk..." 2, The long and... 3, -ABC of Math 4, *Something else The result should look as follows: 3, -ABC of Math 4, *Something else 1, "The less you talk..." 2, The long and... Notice that the title may o may not have or may have more than 1 special character at the beggining Thanks in advance for any help

Alexei Rodriguez

[Andy_L_J]

Ok, I did this:

DECLARE @Table AS Table(
ID Int,
[Name] nVarChar(50))

INSERT @Table(ID, Name)
VALUES(1, '"The less you talk..."')
INSERT INTO @Table
SELECT 2, 'The long and...'
INSERT INTO @Table
SELECT 3, '-ABC of Math'
INSERT INTO @Table
SELECT 4, '*Something Else'

SELECT * FROM @Table
ORDER BY (SELECT CASE WHEN (UNICODE(SUBSTRING(Name,1,1))< 123)
THEN UNICODE(SUBSTRING(Name, 2,1))
ELSE Name End) ASC

Result:

ID Name

3 -ABC of Math
4 *Something Else
1 "The less you talk..."
2 The long and...

The results match what you specified, but what if there is more than one 'special' character at the beginning of the string? EDIT: If you add more data you will find the following:

DECLARE @Table AS Table(
ID Int,
[Name] nVarChar(50))

INSERT @Table(ID, Name)
VALUES(1, '"The less you talk..."')
INSERT INTO @Table
SELECT 2, 'The long and...'
INSERT INTO @Table
SELECT 3, '-ABC of Math'
INSERT INTO @Table
SELECT 4, '*Something Else'
INSERT INTO @Table
SELECT 4, '? A Question'
INSERT INTO @Table
SELECT 4, '#Regions'
INSERT INTO @Table
SELECT 4, 'Sometimes you are right.'
INSERT INTO @Table
SELECT 4, 'Little creatures'

SELECT * FROM @Table
ORDER BY (SELECT CASE WHEN (UNICODE(SUBSTRING(Name,1,1))< 123)
THEN UNICODE(SUBSTRING(Name, 2,1))
ELSE Name End
) ASC

Result:

ID Name

4 ? A Question
3 -ABC of Math
4 #Regions
4 *Something Else
1 "The less you talk..."
2 The long and...
4 Little creatures
4 Sometimes you are right.

This may not be ideal...

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

modified on Saturday, April 17, 2010 12:18 AM

[AlexeiXX3]

Thanks for the reply Its a good start, but it looks like you are only taking into account the first character and in case it is a pecial symbol you take the second, it wont work if you have values like these:

DECLARE @Table AS Table( ID Int, [Name] nVarChar(50))
INSERT INTO @Table SELECT 1, '"The morning..."'
INSERT INTO @Table SELECT 2, 'The long and...'
INSERT INTO @Table SELECT 3, '-ABC of Math'
INSERT INTO @Table SELECT 4, '*Something Else'

SELECT * FROM @Table
ORDER BY (SELECT CASE WHEN (UNICODE(SUBSTRING(Name,1,1))< 123)
THEN UNICODE(SUBSTRING(Name, 2,1))
ELSE Name End) ASC

Notice how "The morning..." appears before The Long and... :( Thanks again for your example

Alexei Rodriguez

[Andy_L_J]

Yes, I have tried quite a few things. You will have to parse the data in the client and perform your sorting there. This is the correct way. The database is for data and not particularly interested in the formatting.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Lost User]

AlexeiXX3 wrote:

Notice that the title may o may not have or may have more than 1 special character at the beggining

The best I can think of is a UDF that uses the REPLACE function to strip those characters. You could add this on a separate view on the table, including the column only when needed- as it would be a very expensive operation (looping all characters for each record). It might be better (performance-wise) to store a redundant version of the Title, without any special characters.

I are Troll :suss: