Solved

SQL Help with string function

Posted on 2011-03-11
14
235 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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