Solved

SQL Help with string function

Posted on 2011-03-11
14
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

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…
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 video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

691 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