Remove Zero

Question

Post reply

Remove Zero

Viewing 10 posts - 16 through 24 (of 24 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

They all work fine.. some are just a tad faster... and easier to undertsand .

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 2

You are right Remi and once again thanks for your help!

surya-236315 SSC Eights! Points: 850 More actions · Answer 3

What is the need to reverse the string? How abt this?

declare @STR varchar(32)

select @STR = '0010A'

select(ltrim(replace('@str','0',' ')))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

You're almost right .

declare @STR varchar(32)

select @STR = '0010A0'

select (ltrim(replace(@str,'0',' ')))

--1 A

select REPLACE(ltrim(replace(@str,'0',' ')), ' ', '0')

--10A0

victor_arreola SSC Enthusiast Points: 114 More actions · Answer 5

What you think about a function?

CREATE FUNCTION noleftzero(@word varchar(20))

RETURNS varchar(20)

AS

BEGIN

WHILE PATINDEX('0%', @word) = 1

BEGIN

set @word = right(@word,len(@word)-1)

END

RETURN(@word)

END

go

/*************/

SELECT dbo.noleftzero('L0001AA') as nozero

I'm not a expert but a think this is good.

From a friend

victor_arreola SSC Enthusiast Points: 114 More actions · Answer 6

CREATE FUNCTION noleftzero(@word varchar(20))

RETURNS varchar(20)

AS

BEGIN

WHILE PATINDEX('0%', @word) = 1

BEGIN

set @word = right(@word,len(@word)-1)

END

RETURN(@word)

END

go

/*************/

SELECT dbo.noleftzero('L0001AA') as nozero

David Burrows SSC Guru Points: 65142 More actions · Answer 7

You seem to be the expert on paterns

Who me! No way!

Jack of all Trades, me, Master of None

Do you have a tutorial or a list of exemples of real life pattern searches (bol doesn't offer much in real life cases)

Nope, fraid not. Took syntax from BOL and experimented a lot!

Far away is close at hand in the images of elsewhere.
Anon.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

This function will run on each line which takes more than than writing it in the select. Secondly, David's version will run only once, therefore much faster than yours which will run once for each leading 0 + 1 :

SUBSTRING([name],PATINDEX('%[^0]%',[Name]),LEN([Name]))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

You're the only one who's continuously sending answers with pathindex and they more often than not beat the crap out of what anyone else can come up with. Maybe you should try to write a tutorial and publish it, or at least send it this way.

hawg SSCertifiable Points: 6044 More actions · Answer 10

Expanding on David's solution, if you wanted to, you could remove the leading zeroes but keep spaces for these zeroes using the following:

REPLICATE(' ', PATINDEX('%[^0]%', [Name]) - 1) + SUBSTRING([name],PATINDEX('%[^0]%',[Name]),LEN([Name]))

Not usually what you want to do BUT there almost always seems to be a need for (at least once) for every scenario.

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011