• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

strip out commas

My query below will test to see if there are 3 commas in the address and if there are 3 commas in the address than @location is used to run a proc I have.... However if there are only 2 commas or 1 comma or 4 commas etc. I want to strip them out all together from the variable called @location.... @location could have the following examples in it..

5222 Michaux Rd, Greensboro, NC, 27410
or
5222 Michaux Rd Greensboro, NC, 27410
or
5222 Michaux Rd Greensboro NC, 27410
or
5222 Michaux Rd Greensboro NC 27410

The only valid one is 5222 Michaux Rd, Greensboro, NC, 27410...

I have functions that will strip out each piece of the address and stuff them into @city, @State and @zip.. Then I remove them from @location so I have each piece of the address.. Then I put them back together again using below:

set @blocation = replace(@blocation, @city, '')
set @blocation = replace(@blocation, @Zip_Parsed, '')
set @blocation = replace(@blocation, @State, '')
set @blocation = rtrim(@blocation)+','+rtrim(@City) +','+rtrim(@State) +','+rtrim(@Zip_Parsed)


What I want to do is if there are not exactly 3 commas in @location remove all commas from @location or if I have to remove commas from @city, @state, and @zip.. Somehow there are commas still being passed...How do I get the commas out if there are not exactly 3?







Declare @count int = LEN(@Location) - LEN(REPLACE(@Location, ',', ''))
		IF @Count = 3
		Begin
		INSERT @AddressValidate exec usp_MelissaData_ValidateAddress @Location

Open in new window

0
cheryl9063
Asked:
cheryl9063
2 Solutions
 
dwe761Software EngineerCommented:
I had no problems using the example below.  Change the @Location and run it each way.  Maybe you've got a different problem?
declare @Location varchar(100),
	@count int

set @Location = 'aaaaaaaaa,aaaaaa,aaaaaaaaaa'
--set @Location = 'aaaaaaaaa,aaaaaa,aaaaa,aaaaa'
--set @Location = 'aaaaaaaaa,aaaaaa,aaaaa,aaaaa,bbbbb'

set @count = LEN(@Location) - LEN(REPLACE(@Location,',',''))
IF @Count = 3 BEGIN
	PRINT 'Run my proc'
END
ELSE BEGIN
	SET @Location = REPLACE(@Location,',','')
	PRINT 'New Location: ' + @Location
END

Open in new window

0
 
LowfatspreadCommented:
do you mean something like this?
IF len(@location+'x') - Len(replace(@location+'x',',','') = 3 
		Begin
		    INSERT @AddressValidate
		      exec usp_MelissaData_ValidateAddress @Location
		    ....  
		end
		else 
		begin
		    set @location=REPLACE(@location,',',' ')
		end

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now