Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table in SQL 2012 which we can call 'Table' for instance. It has items like this in it,



I would like to be able to do this,

select * from Table order by Column

And the result be this,


So it orders the items based on removing the leading underscore characters. It can't just blindly remove the underscore characters though because the underscores might be in the middle of the items.

For instance this does not work,

select Replace(Column, '_', '') from Table order by Replace(Column, '_', '')
share|improve this question
up vote 7 down vote accepted

Assuming col caps out at 255 characters, this doesn't have to perform any checks on length or complicated case expressions:

SELECT col = SUBSTRING(col, PATINDEX('%[a-z]%', col), 255)
  FROM dbo.table
  ORDER BY col;

Updating to show an example that works with varchar even if the value I hard-coded exceeds the size of the column:



SELECT col = SUBSTRING(col, PATINDEX('%[A-Za-z]%', col), 255)
  FROM @t
  ORDER BY col;



Another alternative (that doesn't require hard-coding the length of the string or determining the length of every value in the table) is to use STUFF. Note that this does change how strings without a single alphabetic character will be handled (NULL output instead of being left alone). Those rows will still sort first, they'll just be less meaningful. You can deal with those however you like by adding COALESCE.

SELECT col = STUFF(col, 1, PATINDEX('%[A-Za-z]%', col)-1, '')
  FROM @t
  ORDER BY col;

(This portion of the answer should be credited to @MikaelEriksson.)

share|improve this answer
This only works if the column is fixed length, it wont work on a VARCHAR for instance as you would get an error saying that you passed the end of the string... assuming the VARCHAR is less than the value you hard code that is :) – Mr.Brownstone Aug 15 '12 at 22:01
@Mr.Brownstone sorry but I don't think you're right. It will even handle cases where the string is just _____ (but it will sort those first and not remove any of those characters). – Aaron Bertrand Aug 15 '12 at 22:05
Well I'll be... I retract my statement, it clearly works! Now it begs the question of should it...? :) – Mr.Brownstone Aug 15 '12 at 22:07
@AaronBertrand nice answer. never considered the possibility of multiple _ preceding relevant data – swasheck Aug 15 '12 at 22:08
@Mr.Brownstone why shouldn't it? It's a varchar. I can say DECLARE @x VARCHAR(400) = 'x' - do you expect 399 space characters? It's pretty much the same thing. – Aaron Bertrand Aug 15 '12 at 22:08

How about this?

    when column like '_%' then substring(column, 2, len(column) - 1)
    else column
  end as value
from table
order by 1;

This is off the top of my head and so it is untested...

share|improve this answer
I think if there is more than one leading underscores this will not remove all leading underscores – miracle173 Aug 15 '12 at 22:54

The following is a little bit tricky but the only function I found that could replace a repeating character from the head of a string was the function LTRIM. I did not find any regular expression function (as found in other languages) that can do the work.

Instead of the other solutions currently presented it does the work for a varying number of leading underscores and for a string containing arbitrary characters.

col should be the name of the column. Then the following expression will contain a blank on each position where col contains an underscore and the expression contains a non blank where col contains a non underscore (Of course another character instead of # except underscore and blank can be used to do the trick)

replace(replace(col,' ','#'),'_',' '))

Now I calculate the length of the desired string:

len(ltrim(replace(replace(col,' ','#'),'_',' '))))

Knowing the length of the desired string makes ist easy to calculate the desired string from col by using RIGHT

right(col,len(ltrim(replace(replace(col,' ','#'),'_',' ')))))


now I found another expression to do the job:

substring(col, PATINDEX('%[^_]%', col), len(col)) /* or optionally replace len(...) with 255 to keep consistent with other solutions in this post i.e. avoid using the len function and use a fixed constant instead */


;WITH CTE ([Index], col1) AS (
  SELECT 1 AS [Index], NULL  AS col1 UNION
  SELECT 2 AS [Index], ''    AS col1 UNION
  SELECT 3 AS [Index], 'x'   AS col1 UNION
  SELECT 4 AS [Index], 'x_'  AS col1 UNION
  SELECT 5 AS [Index], '_x'  AS col1 UNION
  SELECT 6 AS [Index], '__x' AS col1
SELECT [Index], SUBSTRING(col1, PATINDEX('%[^_]%', col1), LEN(col1)) AS Result
ORDER BY [Index]


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.