current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 5 down vote favorite

Moving an application from Oracle to SQL Server, I have this pseudo Oracle PL/SQL:

SELECT LTRIM(MyColumn, '-, ') FROM MyTable

I.e. I'm using Oracle's LTRIM with a second argument, specifying the characters to trim from the left side of the string.

Unfortunately, the T-SQL version of LTRIM doesn't allow me to specify the characters to trim.

Currently, I'm rather clueless how to migrate that LTRIM. I'm even thinking about processing the results in my hosting C# application, after I read the MyColumn.

This looks rather inelegant to me.

My question:

Is there any meaningful way of getting an LTRIM-like functionality for T-SQL to pass the characters to trim away?

Edit 1:

I need to replace -, , and from the beginning of the string.

E.g.: 

   -----, ,,, This is ,- a test,---,

would result in 

This is ,- a test,---,

Edit 2:

I strongly hope this isn't an XY problem.

Maybe rewriting my whole query would remove the need for LTRIM altogether, although I would rather focus on porting it as 1:1 as possible and later question the usefulness of the LTRIM.

share|improve this question
1  
There are a number of ways to do this with SUBSTRING and REPLACE. Can you provide some sample data and expected results? – sp_BlitzErik 13 hours ago
    
@JoeObbish In my current (and only) case, only those three -, need to be trimmed from the beginning, independent of the order. – Uwe Keim 13 hours ago
    
@sp_BlitzErik I've added an example. – Uwe Keim 13 hours ago
up vote 4 down vote accepted

Take the suffix of the string starting from the first character which is not a space, comma or hyphen:

declare @str varchar(100) = '   -----, ,,, This is ,- a test,---,'

select  substring(@str,patindex('%[^ ,-]%',@str),len(@str))

Result:

This is ,- a test,---,

Please note that the hyphen, since it is a special character in regular expressions meaning 'range' (e.g. [a-z]), must be either first ([^- ,]) or last ([^ ,-]).

share|improve this answer
    
Can't you just escape the dash with \ to place it anywhere? – majidarif 5 hours ago
1  
@majdarif - no, you can't – Dudu Markovitz 2 hours ago
    
@AndriyM - Thanks for the edit – Dudu Markovitz 27 mins ago
up vote 4 down vote

Yeehaw.

DECLARE @MyTable TABLE ( MyColumn VARCHAR(100) );

INSERT @MyTable ( MyColumn )
    VALUES ( '-----, ,,, This is ,- a test,---,' );

SELECT *, 
        SUBSTRING(mt.MyColumn, ca.p, LEN(mt.MyColumn))
FROM @MyTable AS mt
CROSS APPLY ( SELECT * 
              FROM (VALUES (PATINDEX ('%[^ ,-]%', MyColumn))
            ) AS x (p) ) AS ca;
share|improve this answer
up vote 2 down vote

I don't think you'll be happy with this solution compared to Oracle's ltrim but it does what you want. 

declare @Pattern varchar(32) = '-, ';

select case when MyColumn like '['+@Pattern+']%'
         then right(MyColumn
          , len(MyColumn)-(patindex('%[^'+@Pattern+']%',MyColumn)-1)
          )
       else MyColumn
       end
from MyTable

rextester: http://rextester.com/IXOL62563

share|improve this answer
up vote 2 down vote

Can't say if this is the best solution, but this can be accomplished with SUBSTRING, CHARINDEX, LEFT, LTRIM, and REPLACE. I'm assuming that you're working with 8000 or less characters.

Test code:

DECLARE @test_string VARCHAR(100) = '   -----, ,,, This is ,- a test,---,';
SELECT SUBSTRING(@test_string, CHARINDEX(LEFT(LTRIM(REPLACE(REPLACE(@test_string + 'Z', ',', ''), '-', '')), 1), @test_string + 'Z'), 8000);

Output:

This is ,- a test,---,

Below is an explanation of each part. I added a "Z" to the end to handle the case in which the string only has the characters that need to be removed. First remove all commas:

REPLACE(@test_string + 'Z', ',', '')

Remove all -:

REPLACE(REPLACE(@test_string + 'Z', ',', ''), '-', '')

Remove padded spaces from the left:

LTRIM(REPLACE(REPLACE(@test_string + 'Z', ',', ''), '-', ''))

Grab the first character that isn't space, comma, or -:

LEFT(LTRIM(REPLACE(REPLACE(@test_string + 'Z', ',', ''), '-', '')), 1)

Find the position of the first character that isn't space, comma, or -:

CHARINDEX(LEFT(LTRIM(REPLACE(REPLACE(@test_string + 'Z', ',', ''), '-', '')), 1), @test_string + 'Z')

Take the substring starting at the position that we found:

SELECT SUBSTRING(@test_string, CHARINDEX(LEFT(LTRIM(REPLACE(REPLACE(@test_string + 'Z', ',', ''), '-', '')), 1), @test_string + 'Z'), 8000);
share|improve this answer
    
Will this work if the string has no other characters but commas, spaces and dashes? – ypercubeᵀᴹ 10 hours ago
    
@ypercubeᵀᴹ Nope, thanks for the catch. Fixed the code to handle that case. – Joe Obbish 4 hours ago

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.