Sorting - 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

General SQL Server Forums

New to SQL Server Administration

Sorting

Author

Topic

velliraj
Yak Posting Veteran

59 Posts

Posted - 2010-07-21 : 03:48:54

Hi,

I have a table with column where names are stored.
But the problem is here names are appended with special cahracter at starting.
I have to sort this column by ignoring the special characters

column name
------------
"axnbut"
qwer
--kyirnf
(edrty)

output
------
axnbut
edrty
kyirnf
qwer

Please help, special character can be of any type
For example above i have mentioned some.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-21 : 04:19:36

Use PatIndex and finding the first alphabet.

The below example will be helpful for your understanding.

declare @Test table
(TVal varchar(255))

Insert into @Test
select '"axnbut"' union
select 'qwer' union
select '--kyirnf' union
select '(edrty)' union
select 'Corr Val'

Select Tval ,substring(Tval ,PATINDEX('%[a-z]%',TVal), len(Tval) )
from @Test
order by substring(Tval ,PATINDEX('%[a-z]%',TVal), len(Tval) )

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources