Removing only the leading zero and not all zeros-CodePudding

the goal is to take the numbers in between 2 dashes and I was able to do that but the issue is that I need to remove the leading zero to the returned value. How can I incorporate the LTRIM function or other functions without removing all zeros?

Sample:

123-010-456

Results should be 10

SELECT[Phone],
REPLACE(SUBSTRING([Phone], CHARINDEX('-', [Phone]), CHARINDEX('-', [Phone])),'-','') AS substring
FROM [SalesLT].[Customer]

CodePudding user response：

If your data pattern/format is fixed then below will help

SELECT RIGHT(PARSENAME(REPLACE('123-010-456','-','.'),2),2)

CodePudding user response：

You can combine the following to one query, but for better understanding I break it down to 3 steps.

DECLARE @String nvarchar(20) = '123-010-456'
DECLARE @MiddlePart nvarchar(20)
DECLARE @FirstNonZero int

--Get the middle part
SET @MiddlePart = SUBSTRING(@String,
                 CHARINDEX('-',@String) 1, --Find the 1st dash
                 LEN(@String)-CHARINDEX('-',@String)-CHARINDEX('-',Reverse(@String)) --Find length between two dashes
                 ) 

--Get the First Non zero number
SET @FirstNonZero = (SELECT MIN(x) 
                     FROM (SELECT CHARINDEX('1',@MiddlePart) as x WHERE CHARINDEX('1',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('2',@MiddlePart) as x WHERE CHARINDEX('2',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('3',@MiddlePart) as x WHERE CHARINDEX('3',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('4',@MiddlePart) as x WHERE CHARINDEX('4',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('5',@MiddlePart) as x WHERE CHARINDEX('5',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('6',@MiddlePart) as x WHERE CHARINDEX('6',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('7',@MiddlePart) as x WHERE CHARINDEX('7',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('8',@MiddlePart) as x WHERE CHARINDEX('8',@MiddlePart)>0 
                           UNION
                           SELECT CHARINDEX('9',@MiddlePart) as x WHERE CHARINDEX('9',@MiddlePart)>0 
                     ) a)

--Final
Select SUBSTRING(@MiddlePart,@FirstNonZero,LEN(@MiddlePart)-@FirstNonZero 1)

CodePudding user response：

You could try to extract the middle part and convert it to int - this will remove all leading zeroes while keeping the trailing ones... if required, you can then convert it back to varchar. Following an example:

DECLARE @t TABLE(testval nvarchar(40))

INSERT INTO @t VALUES
('123-010-456')
,('1234-1-456789')
,('12-00010-4')
,('1-0007-4')

SELECT *
      ,SUBSTRING(testval, CHARINDEX('-', testval) 1, CHARINDEX('-', testval, CHARINDEX('-', testval) 1)-CHARINDEX('-', testval)-1)
      ,CAST(SUBSTRING(testval, CHARINDEX('-', testval) 1, CHARINDEX('-', testval, CHARINDEX('-', testval) 1)-CHARINDEX('-', testval)-1) AS INT)
  FROM @t