Post reply

Need Help to remove first word

  • 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

  • June 4, 2014 at 2:55 pm

    #1719023

    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

  • June 4, 2014 at 2:57 pm

    #1719025

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 4, 2014 at 2:57 pm

    #1719026

    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.

    - 😀

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

  • June 4, 2014 at 2:59 pm

    #1719030

    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

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

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