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
add comment

4 Answers 4

up vote 5 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
show 7 more comments

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
add comment

To change the data in the table you could try something like this:

UPDATE TestTable 
  SET Field1 = (CASE WHEN LEFT(Field1,1)='_' THEN RIGHT(Field1,LEN(Field1)-1) 
  ELSE Field1 END);

To retrieve the data in the manner you're requesting, try this query:

SELECT 
    (CASE 
        WHEN LEFT(Field1,1)='_' THEN RIGHT(Field1,LEN(Field1)-1) 
        ELSE Field1 
    END) FROM TestTable
share|improve this answer
    
This would change the underlying data - which may not be desirable. –  Mr.Brownstone Aug 15 '12 at 21:54
    
Yeah, I noticed that and just added the SELECT piece. Guess I should have more carefully read the question eh! –  Max Vernon Aug 15 '12 at 21:55
    
If there are two leading underscores only one will be removed –  miracle173 Aug 15 '12 at 22:51
add comment

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:

substr(col,PATINDEX(col,%[^_]%),len(col))
share|improve this answer
add comment

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.