Solved

SQL Help with string function

Posted on 2011-03-11
14
224 Views
Last Modified: 2012-05-11
My function attached is my starting point for my GetWord Function.. I need to use this function to either get the last word of the value if the the parameter @WordCount is say 3 or get the second to the last word of the value if @WordCount is 2 and so on.. I want to use the @Delimiter to find delimeter in the string such as pipes, commas etc.. Can anyone help with completing what I started?


/****** Object:  UserDefinedFunction [dbo].[ufn_Utility_GetWord] 
   Script Date: 03/11/2011 08:50:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ufn_Utility_GetWord] 
(@value varchar(max),
@WordCount as int,
@Delimiter as varchar(3)) RETURNS varchar(max) 
AS BEGIN 
    RETURN CASE CHARINDEX(' ', @value, 1)  
    WHEN 0 
    THEN @value
            ELSE             
            reverse(SUBSTRING(reverse(@value), 1, 
            CHARINDEX(' ', reverse(@value), 1) -1))
            END 
            
            END

Open in new window

0
Comment
Question by:cheryl9063
  • 7
  • 7
14 Comments
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
I don't understand what you are looking for.  Could you provide an example?

SELECT dbo.ufn_utility_getword('abc 123 xyz',3,' ')
>>> xyz
SELECT dbo.ufn_utility_getword('abc 123 xyz',2,' ')
>>> 123
SELECT dbo.ufn_utility_getword('abc 123 xyz',1,' ')
>>> abc
SELECT dbo.ufn_utility_getword('abc 123 xyz',1,'mmm')
>>> abc 123 xyz

Is this correct?  What if you want to find the... 9th word from last?  Negative @WordCount?
0
 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
Sorry.. Basically this will be used to pull parts of an address out of a string..

SELECT dbo.ufn_utility_getword('5220 michaux rd Greensboro',3,' ')
>>> 'rd'

SELECT dbo.ufn_utility_getword('5220 michaux rd Greensboro',2,' ')
>>> michaux

Then the delimiter part will be used to not pull a delimeter.. So if someone adds a comma or pipe or something to the address..
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
Oh, no problem... Here are two functions I commonly use that should help you out.

They are slightly different, with Split2 capable of handling separators up to 10 characters in length.

I commented out my calls to dropFunc... assuming you don't have that util proc.
--EXEC dropFunc 'Split'
--GO
CREATE FUNCTION dbo.Split (
	@sep char(1),
	@s varchar(8000)
	)
RETURNS table
AS
RETURN (
	WITH Pieces(pn,start,stop) AS (
		SELECT 1,1,CHARINDEX(@sep,@s)
		UNION ALL
		SELECT pn + 1,stop + 1,CHARINDEX(@sep,@s,stop + 1)
		FROM Pieces
		WHERE stop > 0
		)

	SELECT pn,
		SUBSTRING(@s,start,
			CASE
				WHEN stop > 0 THEN stop-start
				ELSE 8000
			END) AS s
	FROM Pieces
	)
GO

--EXEC dropFunc 'Split2'
--GO
CREATE FUNCTION dbo.Split2 (
	@sep nvarchar(10),
	@s nvarchar(4000)
	)
RETURNS table
AS
RETURN (
	WITH Pieces(pn,start,stop) AS (
	SELECT 1,1,CHARINDEX(@sep,@s)
	UNION ALL
	SELECT pn + 1,
		stop + (datalength(@sep)/2),
		CHARINDEX(@sep,@s,stop + (datalength(@sep)/2))
	FROM Pieces
	WHERE stop > 0
	)

	SELECT pn,
		SUBSTRING(@s,start,
			CASE
				WHEN stop > 0 THEN stop-start
			ELSE 4000 END) AS s
	FROM Pieces
	)
GO

Open in new window

0
 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
Can you give me an example how they work?
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
So now you could say:

SELECT *
FROM dbo.Split2(' ','5220 michaux rd Greensboro')

or get an exact piece

DECLARE @x INT
SET @x = 3

SELECT s
FROM dbo.Split2(' ','5220 michaux rd Greensboro')
WHERE pn = @x
0
 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
The problem is we will not know what address is comeing through.. The address is populated into a variable called @Location.. This address could be any variation(or more) below..I need to take each part of the address and run it through another proc for verification.. So for example

5220 michaux rd greensboro nc 27410

or 5220 michaux greensboro nc

or 5220 michaux rd GSO\ North Carolina

or 5220 michaux rd, GSO, NORTHCAROLINA..



Basically I need to find the word in a position(ignore the delimaters) and find out what the third word is or what the last word is etc...
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
I understand there is still something to be done with the @Delimiter... but I'm not entirely sure how you want this to work.

What exactly are you looking to get out of your input.  City, state, etc?

We can easily scrub the input to remove (ignore) delimiters using REPLACE, and I can help you with this, but I don't see how this is going to help.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
My DBA wants me to use only one function.. If you can help tweak what I have below to pull out the first, second third word that would be great.. I could get him to possible show me how to ignore delimeters passed in..


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ufn_Utility_GetWord] 
(@Value varchar(max),
 @WordCount as int,
 @Delimiter as varchar(3)) RETURNS varchar(max) 
AS BEGIN 
	If @WordCount = 4 and @Delimiter = ' '
	RETURN CASE CHARINDEX(' ', @value, 1)  
    WHEN 0 
    THEN @value
            ELSE             
            reverse(SUBSTRING(reverse(@value), 1, 
            CHARINDEX(' ', reverse(@value), 1) -1))
            END 
       If @WordCount = 4 and @Delimiter = ','     
            
            
            
            
            END

Open in new window

0
 
LVL 7

Accepted Solution

by:
mmr159 earned 500 total points
Comment Utility
This should be very close to what you want.

Now if you had in mind to use @delim to contain multiple delimiters, and have them all scrubbed, you will need a little more string magic.
ALTER FUNCTION [dbo].[ufn_Utility_GetWord] (
	@value VARCHAR(8000),
	@token INT,
	@delim VARCHAR(3)
	)
RETURNS VARCHAR(8000) 
AS
BEGIN
	DECLARE @sep CHAR(1),
			@scrub_value VARCHAR(8000),
			@ret_val VARCHAR(8000)
	SET @sep = ' '

	-- This will remove all delims
	SET @scrub_value = REPLACE(@value,@delim,'');

	WITH pieces(pn,start,stop) AS (
		SELECT 1,1,CHARINDEX(@sep,@scrub_value)
		UNION ALL
		SELECT pn + 1,stop + 1,CHARINDEX(@sep,@scrub_value,stop + 1)
		FROM pieces
		WHERE stop > 0
		)

	SELECT @ret_val = s
	FROM (
		SELECT pn,SUBSTRING(
			@scrub_value,
			start,
			CASE
				WHEN stop > 0 THEN stop-start
				ELSE 8000
			END
			) AS s
		FROM pieces
		WHERE pn = @token
		) t
	
	RETURN @ret_val

END
GO


DECLARE @in VARCHAR(8000),
		@word INT,
		@delim VARCHAR(3)

SET @word = 3
SET @delim = '\'

SET @in = '5220 michaux rd greensboro nc 27410'
SELECT dbo.ufn_utility_getword(@in,@word,@delim)
SET @in = '5220 michaux greensboro nc'
SELECT dbo.ufn_utility_getword(@in,@word,@delim)
SET @in = '5220 michaux rd GSO\ North Carolina'
SELECT dbo.ufn_utility_getword(@in,@word,@delim)
SET @in = '5220 michaux rd, GSO, NORTHCAROLINA..'
SELECT dbo.ufn_utility_getword(@in,@word,@delim)

Open in new window

0
 
LVL 1

Author Closing Comment

by:cheryl9063
Comment Utility
Will you marry me?   LOL!!! Thanks so much!!
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
haha - no problem
0
 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
One more question if you dont mind.. I can repost if you want? What if I want to pull from the right if the string instead of the left.. For example 5 words from the end of string.. Which part would I change?
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
Well, in the interest of time... I gotta get work work done :)... you can use REVERSE.

I modified the function slightly to take @from_back BIT argument.  If so, it gets processed from the rear, then flipped before RETURN.
ALTER FUNCTION [dbo].[ufn_Utility_GetWord] (
	@value VARCHAR(8000),
	@token INT,
	@delim VARCHAR(3),
	@from_back BIT
	)
RETURNS VARCHAR(8000) 
AS
BEGIN
	DECLARE @sep CHAR(1),
			@scrub_value VARCHAR(8000),
			@ret_val VARCHAR(8000)
	SET @sep = ' '

	IF @from_back = 1
		SET @value = REVERSE(@value)
	
	-- This will remove all delims
	SET @scrub_value = REPLACE(@value,@delim,'');

	WITH pieces(pn,start,stop) AS (
		SELECT 1,1,CHARINDEX(@sep,@scrub_value)
		UNION ALL
		SELECT pn + 1,stop + 1,CHARINDEX(@sep,@scrub_value,stop + 1)
		FROM pieces
		WHERE stop > 0
		)

	SELECT @ret_val = s
	FROM (
		SELECT pn,SUBSTRING(
			@scrub_value,
			start,
			CASE
				WHEN stop > 0 THEN stop-start
				ELSE 8000
			END
			) AS s
		FROM pieces
		WHERE pn = @token
		) t

	IF @from_back = 1
		SET @ret_val = REVERSE(@ret_val)

	RETURN @ret_val

END
GO


DECLARE @in VARCHAR(8000),
		@word INT,
		@delim VARCHAR(3),
		@backwards BIT

SET @word = 3
SET @delim = '\'
SET @backwards = 1

SET @in = '5220 michaux rd greensboro nc 27410'
SELECT dbo.ufn_utility_getword(@in,@word,@delim,@backwards)
SET @in = '5220 michaux greensboro nc'
SELECT dbo.ufn_utility_getword(@in,@word,@delim,@backwards)
SET @in = '5220 michaux rd GSO\ North Carolina'
SELECT dbo.ufn_utility_getword(@in,@word,@delim,@backwards)
SET @in = '5220 michaux rd, GSO, NORTHCAROLINA..'
SELECT dbo.ufn_utility_getword(@in,@word,@delim,@backwards)

Open in new window

0
 
LVL 1

Author Comment

by:cheryl9063
Comment Utility
Thanks!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now