SQLTeam.com | Weblogs | Forums

Help Needed in occurance


#1

Hi,

I have a sentence and i need to find the last occurrence and need to remove the word from last occurrence.

Ex:

declare @sentence varchar(50) = '333 Manhattan street, Manhattan NY'
Logic : If the occurrence >1 then remove the rest of the sentence from the last occurrence. required output should be

333 Manhattan street.

This is just sample. Like this i have couple records. Any sample query please


#2

Can anyone please help me on this?


#3

Not sure I have understood your question.

Do you just want to truncate at the comma?

If not then pls provide some more examples - showing the different aspects of how this should work


#4

you can use split function if you want to get the words before or after the comma...


#5

Hey Guys, Please find the details below,

with UserAddress as
(
select '124 Florida Avenue,Florida,FL' uninon all
select '320 California street  California,CA' union all
select '333 Manhattan street, Manhattan NY' union all
select '123 Texas Avenue  Texas TX' )


With City(CityName,State) as 

(
Select 'Florida','FL' union all
Select 'California','CA' union all
Select 'Manhattan','NY' union all
Select 'Texas','TX' 
)

I have city list of each state in table and i need to check the city name from city table match it
with the above address data and if the city name found more then once on the UserAddress then remove all the text from last occurrence of the work.

[Code]
Required output:

select '124 Florida Avenue' uninon all
select '320 California street' union all
select '333 Manhattan street' union all
select '123 Texas Avenue'

[/Code]

This just my sample data to play with. My actual data will have correct city list and state.Logically we need to remove the city and state from address column useraddress table

Any sample please


#6

It would be very convoluted to do what you are asking using T-SQL. It can be done, but it would be so painful that I would scream to get out of that misery. Now, if T-SQL had REGEX, it would be a breeze. So, your options as I see it are:

a) Use CLR Regex functions to do what you want. It will take some effort and there is a learning curve to get it going.

b) Use a C# (or any other language) program to read the data, do the required modifications, and save it back to the database.

c) Store the data in a normalized manner, to begin with.


#7

As a one-time cleanup I don't think it is too bad ... but SQL will only do a 99% job, there are bound to be unexpected separator characters in the data that are not anticipated - but when they are found they can be added to the parsing-SQL ...

RegEx/etc. would more likely find more of them ... but may also match something NOT intended!!

If only Humans would enter the data cleanly in the first place ...

So basically we have a list of City and State names and Abbreviations, and we want to remove those from the end of any address string, where they match (and presumably store an ID for the State in the address record instead)

; with UserAddress(Address) as
(
select '124 Florida Avenue,Florida,FL' union all
select '320 California street  California,CA' union all
select '333 Manhattan street, Manhattan NY' union all
select '123 Texas Avenue  Texas TX' UNION ALL
select '124 Texas Avenue  Texas, TX' UNION ALL
select '125 Texas Avenue  Texas ,TX' UNION ALL
select '126 Texas Avenue  Texas , TX' UNION ALL
select 'City Wrong State, Manhattan TX' union all
select 'Unknown City, Unknown, TX' union all
select 'Unknown State, Manhattan, XX'
)
SELECT [Address], [NewAddress] = CONVERT(varchar(100), NULL), [City] = CONVERT(varchar(100), NULL), [State] = CONVERT(varchar(2), NULL)
INTO	#UserAddress
FROM	UserAddress
--
; With City(CityName,State) as 
(
Select 'Florida','FL' union all
Select 'California','CA' union all
Select 'Manhattan','NY' union all
Select 'Texas','TX' 
)
SELECT CityName,State
INTO	#City
FROM City
--
-- Remove any trailing spaces from [Address] - otherwise the rest of the code is full of RTrim statements)
UPDATE	U
SET	Address = RTrim(Address)
FROM	#UserAddress AS U
WHERE	Address LIKE '% '
--
-- Split off valid STATE abbrieviations
DECLARE	@Address varchar(1000)	 -- Working storage
UPDATE	U
SET
	State = S.State,
	@Address = RTrim(LEFT(Address, PATINDEX ('%[ ,;]' + S.State, Address))),
	NewAddress = RTrim(CASE WHEN @Address LIKE '%[,;]' THEN LEFT(@Address, LEN(@Address)-1) ELSE @Address END)
FROM	#UserAddress AS U
	JOIN
	(
		SELECT	DISTINCT State
		FROM	#City
	) AS S
		 ON RTrim(Address) LIKE '%[ ,;]' + S.State
--
-- Split off valid CITY
DECLARE	@City varchar(1000)	 -- Working storage
UPDATE	U
SET
	City = C.CityName,
	@City = RTrim(LEFT(Address, PATINDEX ('%[ ,;]' + C.CityName, NewAddress))),
	NewAddress = RTrim(CASE WHEN @City LIKE '%[,;]' THEN LEFT(@City, LEN(@City)-1) ELSE @City END)
FROM	#UserAddress AS U
	JOIN
	(
		SELECT	DISTINCT CityName
		FROM	#City
	) AS C
		 ON RTrim(NewAddress) LIKE '%[ ,;]' + C.CityName
--
-- Review results
SELECT	*
FROM	#UserAddress
--
DROP TABLE #UserAddress
GO
DROP TABLE #City
GO

#8

Thank you Kristen. Much appreciated .


#9

sorry for late reply, Try this code

SELECT
SUBSTR ('333 Manhattan street, Manhattan NY',1,
INSTR ('333 Manhattan street, Manhattan NY',',',1,(
LENGTH ('333 Manhattan street, Manhattan NY')-(
LENGTH (
REPLACE ('333 Manhattan street, Manhattan NY',',')))))-1) AS OUTPUT
FROM Dual