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

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  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

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  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".

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  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.

  -

• Tee Time

  Hall of Fame

  Points: 3745

  More actions

  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: 183706

  More actions

  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