Need Help to remove first word

Question

Post reply

Need Help to remove first word

gracebite

Right there with Babe

Points: 725
More actions
June 4, 2014 at 2:42 pm

#294080

Hi,

Can you please help me on this

I need to remove part of string from Address field

I tried this

SELECT RIGHT([address], LEN(LTRIM(RTRIM([address]))) - LEN(LTRIM(RTRIM((strtype))))) FROM dbo.address

But I am getting following error

"Invalid length parameter passed to the RIGHT function."

Please help me is there any other procedure

Thanks

Grace

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

maybe something like this ??

declare @address as varchar(300)



set @address = 'removeme 100 1st Street Somewhere'

select @address

select ltrim(stuff(@address,1,charindex(' ',@address),''))

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

What, specifically, are you trying to do?

If you want to remove up to the first space from the front of the string, you can do this:

SELECT SUBSTRING(address, CHARINDEX(' ', address) + 1, LEN(address))

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 3

Hard to say without more details, but it's possible you're getting a number for the second argument of RIGHT that's negative, which causes it to fail.

Try this:



SELECT (LEN(LTRIM(RTRIM([address]))) - LEN(LTRIM(RTRIM((strtype)))))

FROM dbo.address

WHERE (LEN(LTRIM(RTRIM([address]))) - LEN(LTRIM(RTRIM((strtype))))) < 0

See if that returns anything; if so, the rows that are returned are the ones that are causing issues.

- 😀

Tee Time Hall of Fame Points: 3745 More actions · Answer 4

Tee Time

Hall of Fame

Points: 3745

June 4, 2014 at 2:57 pm

#1719027

RIGHT is expecting an integer to tell it how many characters you want. Replace "some number" with your number.

RIGHT([address], LEN(LTRIM(RTRIM([address])), some number)

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

Just another option based on the fact that you're using 2 columns.

WITH SampleData AS(



    SELECT 'This is a test' [address], 'This' strtype UNION ALL

    SELECT 'Short', 'And Long' UNION ALL

    SELECT 'Empty strtype', '' UNION ALL

    SELECT 'NULL strtype', NULL

)

SELECT STUFF( [address], 1, ISNULL(LEN(strtype), 0), '')

FROM SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2