Trimming Leading Zeros

Downloads
25208.zip

I'm looking for a SQL Server 7.0 function that trims leading zeros in columns—a function similar to LTRIM(), which trims only spaces. However, I can't find a system-supplied function to do this task. Any ideas?

You're right, no native function trims leading zeros. However, let's look at two ways to solve your problem. The first solution uses a combination of built-in string functions. Let's use the table and sample data that Listing 1 shows to walk through the solution. First, you have to figure out a way to locate the position of the first nonzero character in Listing 1's col1 because you need to use the position to determine which characters you should discard. You can calculate the position of the first nonzero character by using the PATINDEX() function, which returns the position of a pattern within a string, as follows:

SELECT
   col1,
   PATINDEX('%\[^0\]%', col1) AS pos
FROM Zeros

This query returns the output that Table 1 shows. Notice the two exceptions in Table 1—the string that's composed only of zeros and the NULL. Assuming that you want to return an empty string when the original string contains only zeros, you can use a trick to make PATINDEX() return the length of the string plus 1 instead of 0. This strategy trims all zeros because your code is going to extract only the characters that follow the leading zeros.

Here's the trick. First, concatenate a nonzero character to col1 inside the PATINDEX() function. (If the column's value contains only zeros, the PATINDEX() function locates the concatenated nonzero character and returns its position.)

PATINDEX('%\[^0\]%', col1 + 'A')

Now, use the SUBSTRING() function to extract the characters following the leading zeroes from col1:

SELECT
   col1,
   SUBSTRING(col1, PATINDEX('%\[^0\]%', col1+'A'), LEN(col1)) AS new_col1
FROM Zeros

Table 2 shows the output for this query. If you want to return NULL in new_col1 when col1 contains NULL, you don't need to change the query above because the NULL in col1 already causes the expression to result in NULL.

The second way to trim leading zeros incorporates the LTRIM() function you wanted to emulate. First, use the REPLACE() function to substitute all existing zeros with spaces. Next, use the LTRIM() function to eliminate the leading spaces. Then, use REPLACE () again to turn the spaces back to zeros. The following statement shows this process:

SELECT
  col1,
  REPLACE(LTRIM(REPLACE(col1, '0', ' ')), ' ', '0') AS new_col1
FROM Zeros