Remove Leading and Trailing Zeros

DECLARE @BankAccount TABLE (AccNo VARCHAR(15))

INSERT @BankAccount SELECT '01010'

INSERT @BankAccount SELECT '0010200'

INSERT @BankAccount SELECT '000103000'

  

SELECT * FROM @BankAccount

  

--Methods to remove leading zeros

  

-- 1.)  converting to integer data type

SELECT CONVERT(INT,AccNo) AccNo FROM @BankAccount

-- NN - note, this method will only work if the data are clean

  

-- 2.)  using SUBSTRING 

  

SELECT SUBSTRING(AccNo,PATINDEX('%[^0]%',AccNo),LEN(AccNo)) AccNo FROM @BankAccount

  

-- 3.)  using REPLACE,LTRIM & RTRIM

  

SELECT REPLACE(LTRIM(REPLACE(AccNo,'0',' ')),' ','0') AccNo FROM @BankAccount

--To remove both leading & trailing zeros

  

SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount

Reverse a string without using T-SQL REVERSE() function

DECLARE @StringToReverse VARCHAR(55)

SET @StringToReverse = 'Reverse a string with out using REVERSE() function'

 

;WITH cte AS (

      SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln

      UNION ALL

      SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln

      FROM cte

      WHERE ln >= 1)

SELECT @StringToReverse AS String, revStr

FROM cte

WHERE ln = 0