Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Help with string function

Posted on 2011-03-11
14
Medium Priority
?
244 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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