Remove certain unknown amounts of character from a string

Question

Post reply

Remove certain unknown amounts of character from a string

GBeezy

SSCarpal Tunnel

Points: 4650
More actions
November 9, 2022 at 2:17 pm

#4110537

All,

I have a field, SourceID, that looks like the folloing
V0008048
V0011675
V0000145

In each instance I need to remove the V and all of the zeros, so I should return the following:
8048
11675
145

Ive tried a replace, trims and nothing will work. Is this possible?

Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182860 More actions · Answer 1

Eirikur Eiriksson

SSC Guru

Points: 182860

November 9, 2022 at 2:56 pm

#4110542

Have a look at this thread

😎

Jeff Moden SSC Guru Points: 1003527 More actions · Answer 2

If it will always be that you have a non-numeric first character and the rest are numeric, this will work very quickly...

 SELECT CONVERT(INT,SUBSTRING(yourstring,2,8000))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003527 More actions · Answer 3

Eirikur Eiriksson wrote:

Have a look at this thread
😎

That's a long thread. You might want to provide the post number for where we ended up on that thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182860 More actions · Answer 4

Jeff Moden wrote:

Eirikur Eiriksson wrote:
Have a look at this thread
😎
That's a long thread. You might want to provide the post number for where we ended up on that thread.

Good point Jeff!

😎

Here is a link to the functions