Better Techniques for Trimming Leading Zeros in SQL Server

Better techniques for trimming leading zeros in SQL Server?

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

Removing leading zeroes from a field in a SQL statement

select substring(ColumnName, patindex('%[^0]%',ColumnName), 10)

How to remove leading zero(s) from SQL

cast col1 to int so the leading zeroes are removed and cast the int to varchar for concatenation.

COALESCE(CASE WHEN col1 = '' THEN '' ELSE cast(cast(col1 as int) as varchar(255))+ ' ' END, '')  + 
COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '')  + 
COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '') 

How to remove special characters and leading zeros from field in sql

thanks Alan for the detail, it is really great. i am just posting this if anyone needs a simplified version.

select * from myTable 
where replace(ltrim(rtrim(replace(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(isnull(part_number,''),'-',''),'-',''),'*',''),' ',''),'.',''),',',''),'/',''),'\',''),'#',''),':',''),'''',''),'(',''),')',''))), '0', ' '))), ' ', '0') = '8333152701'

How to ignore 00 (two leading zeros) in Select query?

You need to apply TRIM function on both - column and the value you want to filter by:

SELECT * FROM MyTable
WHERE TRIM(LEADING '0' FROM refNumber) = TRIM(LEADING '0' FROM '00123') -- here you put your desired ref number

---

Related Topics