How To Remove Leading Characters in SQL

Question

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

Answer 1

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.)

Answer 2

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

Answer 3

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

Answer 4

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