How can I retrieve the data without leading zeroes in SQL?
I was working with a table which is a database related to storing identification numbers with leading zeroes. Under this scenario, my task is to retrieve the data without the leading zeroes. How can I structure my SQL query to retrieve the data without zeroes?
To remove leading zeroes SQL, you can use the particular function of “LIRIM()” or “ CAST()”. Both could be implemented in combination with functions such as “SUBSTRING()” or “ TRIM()”. You can choose between both based on which system you are using.
Here is the example given using SUBSTRING () and CAST().
Suppose, you have a column named ID which includes numbers with leading zeroes in the table whose name is “YourTable”. You can use the SUBSTRING () function for this specific scenario:-
SELECT CAST(SUBSTRING (ID, PATINDEX(%[^0], ID + ' '), LEN(ID)) AS JNT) AS ID_ No_ Leading_Zeros”This above query of SQL will retrieve the column values of ID without leading zeroes from your particular table of “YourTable”.