Order by / CharIndex question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order by / CharIndex question

Author

Topic

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2002-02-12 : 16:43:59

Hello,

I'm working on a query, but I'm having a tough time. Here is what I need to do. I have a part_id column that can be in the format x-xxxxx-xx-x, where an x can really be any number of characters, or none at all, but there is always at least one dash. What I need to do is order the list by the number of characters between the first and second dash.

I can sort the most typical format, which is A-12345-AB. Where I run into problems is when there is no second dash, say A-12345.

Here is some (caution, it's ugly) of what I have so far:
-- this gives me the string after the first dash
substring(part_id, charindex('-', part_id)+1, len(part_id) -charindex('-', part_id))

-- this gives me the portion between the two dashes (except is has the last dash, I haven't figured out why yet) unless there is no second dash
substring(substring(part_id, charindex('-', part_id)+1, len(part_id) -charindex('-', part_id)), 1, charindex('-', substring(part_id, charindex('-', part_id)+1, len(part_id) -charindex('-', part_id)))),

Anyone have any ideas? Thanks for any help.

Thanks,
Nick

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-12 : 16:50:35

Concatenate a dash to the part_id column; if there's only one dash, then it will read to the end, if there's more than one, it will read to the 2nd dash and ignore the one at the end:

substring(part_id, charindex('-', part_id)+1, len(part_id) -charindex('-', part_id + '-'))

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2002-02-12 : 17:37:05

Thanks Rob. If you heard a big 'DOH,' that was me.

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2002-02-12 : 21:37:39

Ok, I've got another question. If I have A-12345-A, A-12345-1, A-12345-11, and A-12345-2 how can I also order them in this order: -A, -1, -2, -11? So, basically I need the numbers with an alpha suffix first and then the rest in numeric order. Thanks again for any help.

Thanks,
Nick

Nazim
A custom title

1408 Posts

Posted - 2002-02-13 : 00:22:46

This should do what you are looking for

select a
from tablename
order by substring(a,1,len(a)-charindex('-',reverse(a))),
ascii(substring(a,len(a)-charindex('-',reverse(a))+2,1))
+ascii(substring(a,len(a)-charindex('-',reverse(a))+3,1) )

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2002-02-13 : 11:14:40

Thanks for the help Nazim. That works, but what if I have A-12345-AA? The order should then be -A, -AA, -1, -2, -11. -AA will appear at the end with the way the query is now. Any thoughts? Thanks again for all of your help.

Thanks,
Nick

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-13 : 11:42:17

Take a look in Books Online for the Replace() and Parsename() functions. You can use Replace to change the "-" into ".", which Parsename can then split.

SELECT A FROM tableName
ORDER BY ParseName(Replace(A, '-', '.'), 2), ParseName(Replace(A, '-', '.'), 3), ParseName(Replace(A, '-', '.'), 1)

Be warned: this is one of my trademark oddball-ways-of-doing-things solutions, so it might not work! You'll have to play with the Parsename settings to make sure you getting the correct piece.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources