I came up with another option, and I think it is easier. It uses patindex to find the first non-zero character.

select substring(@varstr,patindex('%[^0]%', @varstr),len(@varstr) - patindex('%[^0]%', @varstr) + 1)

I guess not enough people complained about the answer to this question.

I have to agree with Jeff. This was an awesome thread and it demonstrates what a community can do when it works together for a common purpose. Hats...

See if you can use this code to strip leading zeros and if it helps your query.

declare @varstr nvarchar(50);

set @varstr = '000045691823';

select substring(@varstr,patindex('%[A-Za-z1-9]%', @varstr),len(@varstr) - patindex('%[A-Za-z1-9]%', @varstr) + 1)

set @varstr...

You should still be able to use the code sample above to develop a solution to your problem at work.

Looks like another "cursor man" out there if you...

May I suggest you read this article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Also you may want to read this blog: The Flip...

Does this help?

create table dbo.xtable (

ID int,

val1 varchar(30) null,

val2 varchar(30) null

);

create table #tmp (

ID...

Well, you need to provide a clearer picture of what you...

Not...

Still confused. One, the formatting doesn't allow me to easily see what is in what column. Also, Are we updating existing data? Your original post really did...

Three years experience, asking questions like this? More like 1 month of experience over 36 months.

RBarryYoung (4/14/2009)

Manie Verster (4/14/2009)

RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every...

GilaMonster (4/14/2009)

I saw it. I had a half-written, very snarky reply that I deided was better not posted....

Okay, try this instead:

create table dbo.xtable (

ID int,

val1 varchar(30) null,

val2 varchar(30) null

);

create table #tmp (

...