• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Removing leading zeros from a column without editing actual value

 
Harsh Bhasin
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I have a database (using sql server) with a column (type nvarchar) with leading zeros.

I am trying to get the length of the values in the column without leading zeros. Also I am not allwed to edit the actual values in the database.

I have tried using CAST(Column name AS INT) but I keep running into the issue of how do I store the int value without disturbing the initial value and then get the lenght of the new integer value?

Any help is appreciated.

Thank you
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harsh Bhasin wrote:Hello,

I have a database (using sql server) with a column (type nvarchar) with leading zeros.

I am trying to get the length of the values in the column without leading zeros. Also I am not allwed to edit the actual values in the database.

I have tried using CAST(Column name AS INT) but I keep running into the issue of how do I store the int value without disturbing the initial value and then get the lenght of the new integer value?

Any help is appreciated.

Thank you


IMNSHO, CAST() is not a great approach, as it modifies both the the data type and the value, not just the value. That is, it changes two things in place of one.

Anyway, there are a number (no pun intended) of ways to do this.

Pinal Dave suggests using PATINDEX and SUBSTRING. It sounds complicated, but all it does is find the first non-zero (and non-space, in his example) and substrings from there. That is likely the most straightforward approach.

This post in the SQL Server forums mentions CAST() (which i would not use), has the approach mentioned above a few times, and has a cute REPLACE(RTRIM(REPLACE))) method. RTRIM would be the best if it could do zeroes, unfortunately, SQL Server limits it to spaces. However, you can REPLACE the zeroes with spaces first, and then do the RTRIM. Of course, if any other zeroes were in the string, they too are now spaces, and need to be put back to zeroes, hence the second REPLACE. This method is easier to understand, perhaps, but takes 3 functions instead of 2, and the work is not as straightforward. Furthermore, it assumes there are no spaces in the string that will mess this up. If not guaranteed, you would actually have to remove all spaces before starting, which would require yet another REPLACE.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harsh Bhasin wrote:I have tried using CAST(Column name AS INT) but I keep running into the issue of how do I store the int value without disturbing the initial value and then get the lenght of the new integer value?

There are a few SQL whizzkids active in these forums, so they probably can give you a few solutions. But let's see what I (being a Java developer) can think of:
A/ cast to int, cast to varchar and use length function (problem of this approach is the fact that it won't work as alphanumeric characters exist
B/ get the length of the substring containing no leading zeroes (seems to be the most solid option)

Certainly the biggest challenge for (B) is probably how to determine the number of leading zeros and thus where to start the substring (as it could be none, 1, 2, maybe even 5,...). But it seems SQL Server has PATINDEX, a function returning the starting position of the first occurrence of a pattern in a specified expression. And that makes it very easy.

Here are the different steps:
1/ To skip the leading zeros, you need to find the location of the first non-0 character, which can be done using this pattern [^0]
2/ So using this function PATINDEX('%[^0]%', TheColumn) will give you the starting position of the first non-0 character
3/ Use (2) appropriately in the SUBSTRING function to get the string without leading zeros: SUBSTRING(TheColumn, PATINDEX('%[^0]%', TheColumn), LEN(TheColumn))
4/ now simply get the length of (3) and use it in a SELECT statement:

Hope it helps!
Kind regards,
Roel
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:There are a few SQL whizzkids active in these forums, so they probably can give you a few solutions.

And it seems one of them beat me to it

(But I am happy to see Brian suggests a similar solution )
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:(But I am happy to see Brian suggests a similar solution )

Actually, i think you just schooled me. I completely forgot the OP asked about the length, and here i was off doing substrings. Silly me.

LEN(string) - PATINDEX('%[^0]%') is the answer. Duh! Oops, there might be no zeroes and nulls are bad, so, LEN(string) - ISNULL(PATINDEX('%[^0]%'), 0), though that might be off by one. I forget which functions are offsets and which are indexes.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:LEN(string) - PATINDEX('%[^0]%') is the answer. Duh! Oops, there might be no zeroes and nulls are bad, so, LEN(string) - ISNULL(PATINDEX('%[^0]%'), 0), though that might be off by one.

I think you forgot the second parameter of the PATINDEX function
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Brian Tkatch wrote:LEN(string) - PATINDEX('%[^0]%') is the answer. Duh! Oops, there might be no zeroes and nulls are bad, so, LEN(string) - ISNULL(PATINDEX('%[^0]%'), 0), though that might be off by one.

I think you forgot the second parameter of the PATINDEX function

Yep. Want to know why i made that mistake? Too bad, i'm telling you anyway. :^P I wasn't going to post a complete solution as we're told to help, not to just hand out answers. But, the pattern in PATINDEX is not easily understood, and Pinal's included a space, so i went back and added it in before posting. I then completely missed the second parameter.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:I wasn't going to post a complete solution as we're told to help, not to just hand out answers.

That doesn't ring any bell
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I might be a bit slow here, but what DB are we talking about?
If it's Oracle then TRIM, surely?

LENGTH(TRIM(LEADING 0 FROM <your column>))
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:I might be a bit slow here, but what DB are we talking about?


OP:
Harsh Bhasin wrote:Hello,

I have a database (using sql server)
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought I must have missed something!
And I read it three times as well!

 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:I thought I must have missed something!
And I read it three times as well!


I saw it the first time, but took me a couple reads after you asked about it. It's not only you. It's catchy too.
 
Harsh Bhasin
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Roel and Brian for talking through the solution. Had a good time reading your conversations.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic