• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Removing leading zeros from a column without editing actual value

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I thought I must have missed something!
And I read it three times as well!

 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Roel and Brian for talking through the solution. Had a good time reading your conversations.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic