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
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?
Roel De Nijs wrote:There are a few SQL whizzkids active in these forums, so they probably can give you a few solutions.
Roel De Nijs wrote:(But I am happy to see Brian suggests a similar solution )
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.
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
Dave Tolls wrote:I might be a bit slow here, but what DB are we talking about?
Harsh Bhasin wrote:Hello,
I have a database (using sql server)
Dave Tolls wrote:I thought I must have missed something!
And I read it three times as well!
Consider Paul's rocket mass heater. |