Replace Command

Question

Post reply

Replace Command

JPLeBlanc

Old Hand

Points: 365
More actions
January 26, 2009 at 9:39 am

#199284

Maybe I didn't look hard enough, but i havent been able to find a quick solution for this and if i assume correctly (which i typically dont 🙂 ) wouldn't the following statement replace the '0' in any part of the string? I would like to remove any '0' from the beginning only, as you can see in the table sample there could be only 1 '0', but sometimes there will be 2 '0'.
replace([Login],'0','')
Old New
Login Login
02191 2191
00551 511
02272 2272
00636 636
00485 485
02061 2061
00705 705 - current statement would make this 75?
00817 817
jc1234 jc1234
jc0123 jc0123

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Just convert to INT and you're done...

--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)

JPLeBlanc Old Hand Points: 365 More actions · Answer 2

but that would be too easy 🙂

can this be done with replace?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Sometimes REPLACE isn't the right tool for the job. I agree with Jeff, just convert to an INT.

JPLeBlanc Old Hand Points: 365 More actions · Answer 4

Sorry guys,

Just changed the problem. Please see the edited table data last two rows. Those exist and cannot be changed as they will be used.. 🙁

JPLeBlanc Old Hand Points: 365 More actions · Answer 5

I did find some references online to Ltrim and Rtrim, but i dont really understand the syntax.

edit: nevermind it looks like ltrim and rtrim only remoce spaces not characters.

Lowell SSC Guru Points: 323518 More actions · Answer 6

Follow with what Jeff And Lynn said; repalce with an int where isnumeric is true:

UPDATE YOURTABLE SET Login = Convert(varchar,CONVERT(INT,Login)) Where IsNumeric(Login) =1

the rows like 'jc4444' will not be numeric, thus problem solved.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JPLeBlanc Old Hand Points: 365 More actions · Answer 7

JPLeBlanc

Old Hand

Points: 365

January 26, 2009 at 11:34 am

#932746

this did the trick, thanks once again everyone.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Be careful... ISNUMERIC should never be used as an "IS ALL DIGITS" function... For example...

SELECT ISNUMERIC('1D3'), ISNUMERIC('1E3'), ISNUMERIC('1.3'), ISNUMERIC('-13')

--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: 1004363 More actions · Answer 9

The proper way to do such a detection is similar to the following where '1D3' could be a column name...

[font="Courier New"] SELECT 1 AS IsAllDigits

WHERE '1D3' NOT LIKE '%[^0-9]%'[/font]

--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)