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
Insert Distinct Values from One Table into Another Table
Laravel Eloquent Mutiple Counts With Different Conditions in One Query
How to Display Half Records from Table - MySQL
Convert Varchar Mmddyyyy to Mm/Dd/Yyyy Datetime and Select the Most Recent Date Only
Splitting String into Multiple Rows in Oracle
How to Divide Sum in Number from Count in Having
Mysql Select Rows on First Occurrence of Each Unique Value
Reactjs Connection With Database
Postgresql How to Insert a Value With Double Quotes Inside Double Quotes
Way to Find Data of a SQL Table With Same Status for Consecutive 3 Days
Copy and Insert to Same Table No Duplication and With Minor Changes to Value
Grouping But With Keeping All Non-Null Values
Use Current Date as Default Value for a Column
Replace Default Null Values Returned from Left Outer Join
I Want to Give Serial No in My Query According to Group
Find Employee Name Who Is Having Highest Salary in Given Month in SQL