Solved

SQL Help with string function

Posted on 2011-03-11
14
227 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
ID: 35109669
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
ID: 35109731
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
ID: 35109851
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
ID: 35109867
Can you give me an example how they work?
0
 
LVL 7

Expert Comment

by:mmr159
ID: 35109873
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
ID: 35109910
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
ID: 35109995
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:cheryl9063
ID: 35110044
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
ID: 35110589
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
ID: 35110830
Will you marry me?   LOL!!! Thanks so much!!
0
 
LVL 7

Expert Comment

by:mmr159
ID: 35111232
haha - no problem
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 35111567
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
ID: 35111691
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
ID: 35111748
Thanks!
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

895 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