Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

Column

_one
_two
three_blah
four_blah

I would like to be able to do this,

select * from Table order by Column

And the result be this,

four_blah
one
three_blah
two

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

3 Answers 3

up vote 6 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:

DECLARE @t TABLE(col VARCHAR(32));

INSERT @t VALUES
('_one'),
('_two'),
('three_blah'),
('four_blah');

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

Results:

col
----------
four_blah
one
three_blah
two

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
1  
@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?

select 
  case 
    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,' ','#'),'_',' ')))))

EDIT:

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 */

E.g.

;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
FROM CTE
ORDER BY [Index]

Returns:

Index|Result
    1|NULL
    2|''
    3|'x'
    4|'x_'
    5|'x'
    6|'x'
share|improve this answer

Your Answer

 
discard

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.