SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 stringy
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/20/2005 :  03:31:19  Show Profile  Reply with Quote
There seems to be some occasional requests to parse words that are contained in various types of character fields. My code deals with some of these requirements including extracting initials etc.

It aint pretty but it is easy to tweak to your requirements. I think it all works as expected though some may not be thoroughly tested so any constructive comments would be greatly appreciated

/* Sprocs for String Handling. 

elwoos
oct 2003
*/

-- This one is a bit redundant in T-SQL but this was all written originally in VBA

CREATE FUNCTION dbo.fn_Strip 
	(@InString varchar(4000), 
	 @Char varchar(1) = ' ')
RETURNS varchar(4000) 
AS
BEGIN
	SELECT @InString = REPLACE(@InString,@Char,'')
	RETURN (@InString)
END
GO



-- -- Public Function StripAll(sInString As String, Optional SearchChars)
-- -- ' strip all characters in SearchChars from sInstring
-- -- 
-- -- ' REQUIRES: strip function above
-- -- 
-- -- ' if no value given for searchchars , use default of a space
-- -- ' which is equivalent to using the strip function

-- -- An alternative implementation would be from 
-- -- Seventhnight

-- -- Declare @myTable Table (textVal varchar(100))
-- -- Insert Into @myTable
-- -- Select 'ABCDEFGABCDE' Union 
-- -- Select 'Bill Gates' Union 
-- -- Select 'Bubblicious Gum' Union 
-- -- Select 'Captian Crunch' Union 
-- -- Select 'Big Boy ''Bud''' Union 
-- -- Select 'That''s Enough'
-- -- 
-- -- Declare @remove varchar(10)
-- -- Set @remove = 'BD'
-- -- 
-- -- Select * From @myTable
-- -- 
-- -- while exists(Select * From @myTable Where patindex('%['+@remove+']%',textVal)>0)
-- -- Begin
-- -- 	
-- -- 	Update @myTable
-- -- 	Set textVal = stuff(textVal,patindex('%['+@remove+']%',textVal),1,'')
-- -- 	From @myTable
-- -- 	Where patindex('%['+@remove+']%',textVal)>0
-- -- End
-- -- 
-- -- Select * From @myTable
-- -- 

-- My (far less elegant version) is
CREATE FUNCTION dbo.fn_StripAll
	(@InString varchar(4000), 
	 @SearchChars varchar(4000) = ' ')
RETURNS varchar(4000) 
AS
BEGIN
	DECLARE @Char varchar(1)
	WHILE LEN(@SearchChars) > 0
		BEGIN
			SET @Char = LEFT(@SearchChars,1)
-- assumes that the characters in @SearchChars are unique
			SET @InString = dbo.fn_Strip(@InString, @Char)
			SET @SearchChars = RIGHT(@SearchChars, LEN(@SearchChars)-1)
		END

	RETURN (@InString)
END
GO

-- -- ' returns the location of the last 'lookfor' in the string

CREATE FUNCTION dbo.fn_InStrRev
	(@LookIn varchar(4000), 
	 @LookFor varchar(4000) = ' ')
RETURNS INT 
AS 
BEGIN
	IF @LOOKFOR IS NULL	RETURN (0)
	DECLARE @sTemp varchar(4000)
	SET @sTemp = REVERSE(@LookIn)
	RETURN (LEN(@LookIn) - PATINDEX(@LookFor,@sTemp) + 1)

END

GO

-- -- ' returns all characters after the last deliimiter

CREATE FUNCTION dbo.fn_LastWord
	(@LookIn varchar(4000), 
	 @Delimiter varchar(1) = ' ')
RETURNS varchar(4000)
AS 
BEGIN
	DECLARE @ANSWER VARCHAR(4000)
-- -- 'can't do it if nowt to look for
	IF @DELIMITER is NULL SET @ANSWER = ''
	DECLARE @Count INT
	SET @Count = dbo.fn_InStrRev(@LookIn,@Delimiter)
	IF @Count > Len(@LookIn) SET @ANSWER = @LookIn
	ELSE
	SET @ANSWER = Right(@LookIn, Len(@LookIn) - @Count)
RETURN @ANSWER
END
GO

-- -- ' returns all the characters after the first delimiter

CREATE FUNCTION dbo.fn_LastWords
	(@LookIn varchar(4000), 
	 @Delimiter varchar(1) = ' ')
RETURNS varchar(4000)
AS 
BEGIN
	DECLARE @ANSWER VARCHAR(4000)
	IF @Delimiter IS NULL SET @ANSWER = ''
	DECLARE @Count INT
	SET @COUNT = CHARINDEX(@Delimiter, @LookIn)
	IF @COUNT = 0 SET @ANSWER = '' -- not found
	ELSE
	SET @ANSWER = RIGHT(@LookIn, Len(@LookIn) - (@COUNT + 1))
RETURN @ANSWER
END
GO

-- -- ' returns all the characters up to the delimiter
CREATE FUNCTION dbo.fn_FirstWord
	(@LookIn varchar(4000), 
	 @Delimiter varchar(1) = ' ')
RETURNS varchar(4000)
AS 
BEGIN
	DECLARE @ANSWER VARCHAR(4000)
	IF @Delimiter IS NULL SET @ANSWER = ''
	DECLARE @Count INT
	SET @COUNT = CHARINDEX(@Delimiter, @LookIn)
	IF @COUNT = 0 SET @ANSWER = '' -- not found
	ELSE
	SET @ANSWER = left(@LookIn,@COUNT)
RETURN @ANSWER
END
GO


-- -- ' counts number of times that cLookFor appears in sLookin

CREATE FUNCTION dbo.fn_CountChars
	(@LookIn varchar(4000), 
	 @LookFor varchar(1) = ' ')
RETURNS INT
AS 
BEGIN
-- -- ' exit if null string
IF @LookIn IS NULL OR @LookFor IS NULL RETURN 0
-- -- ' only take fist character to lookfor
DECLARE @COUNT INT
SET @COUNT = 0
WHILE LEN(@LookIn) > 0
	BEGIN
		IF LEFT(@LOOKIN,1) = @LookFor 
			BEGIN
				SET @COUNT = @COUNT + 1
			END
		SET @LookIn = RIGHT(@LOOKIN, LEN(@lookin) -1)
	END
RETURN (@COUNT)
END
GO
-- -- ' this function extracts initials from a string.

CREATE FUNCTION dbo.fn_GetInitials
	(@Name varchar(4000))
RETURNS varchar(4000)
AS 
BEGIN
	DECLARE @OddsNSods varchar(4000)
	SET @OddsNSods = '"(){}[];:@!£$%^&*_/*-+\|<> ''-'
	DECLARE @RESULT VARCHAR(4000) -- used to build result string
	SET @RESULT = ''
	SET @Name = dbo.fn_StripAll(@Name, @OddsNSods)
	IF @Name is NULL 
		BEGIN
			RETURN NULL
		END
	ELSE
		BEGIN
			DECLARE @curWord varchar(4000)
			DECLARE @restString varchar(4000)
			SET @restString = @name
			WHILE LEN(@restString) > 0 -- This may be a bug
				BEGIN
					SET @curWord = dbo.fn_Trim(dbo.fn_FirstWord(@restString, ' '))
					SET @restString = dbo.fn_TRIM(RIGHT(@restString, LEN(@restString)-LEN(@curWord)))
					IF @RESULT = ''	
						BEGIN
							SET @RESULT = LEFT(@curWord,1)
						END
					ELSE 
						BEGIN
							SET @RESULT = @RESULT + LEFT(@curWord,1)
						END
				END
		END
-- -- ' if the following character is not at the end or the string is not null
-- -- ' then it's an initial
-- -- GetInitials = result
	RETURN (@RESULT)
END
GO


A sarcasm detector, what a great idea.

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/20/2005 :  14:20:37  Show Profile  Reply with Quote
quote:
WHILE LEN(@restString) > 0 -- This may be a bug

HA HA HA !!!
Gotcha!

PS. Nice though

rockmoose
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/23/2005 :  06:36:56  Show Profile  Reply with Quote
Just testing to see if anyone would actually read any of it

A sarcasm detector, what a great idea.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/23/2005 :  07:25:46  Show Profile  Reply with Quote
I got the sqlteamforums rss PostFeed
and filter out all the interesting posts !
"bug" is one of the keywords I use

rockmoose
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 05/25/2005 :  01:51:31  Show Profile  Visit Igor2004's Homepage  Reply with Quote
please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115

Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115


fn_InStrRev -> RAT()
-- RAT(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
-- Return Values smallint
-- Parameters
-- @cSearchExpression nvarchar(4000) Specifies the character expression that RAT( ) looks for in @cExpressionSearched.
-- @cExpressionSearched nvarchar(4000) Specifies the character expression that RAT() searches.
-- @nOccurrence smallint Specifies which occurrence, starting from the right and moving left, of @cSearchExpression RAT() searches for in @cExpressionSearched. By default, RAT() searches for the last occurrence of @cSearchExpression (@nOccurrence = 1). If @nOccurrence is 2, RAT() searches for the next to last occurrence, and so on.

fn_LastWord -> GETWORDNUM (@LookIn, GETWORDCOUNT(@LookIn, @cDelimiters), @cDelimiters)
fn_FirstWord -> GETWORDNUM (@LookIn, 1, @cDelimiters)

-- GETWORDCOUNT(@cString, @cDelimiters])
-- Parameters @cString nvarchar(4000) - Specifies the string whose words will be counted.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- GETWORDNUM(@cString, @nIndex[, @cDelimiters])
-- Parameters @cString nvarchar(4000) - Specifies the string to be evaluated
-- @nIndex smallint - Specifies the index position of the word to be returned.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.


fn_CountChars -> OCCURS()

-- OCCURS Returns the number of times a character expression occurs within another character expression.
-- OCCURS(@LookIn , @LookFor )
-- Parameters
-- @LookIn nvarchar(4000) Specifies a character expression that OCCURS() searches for within @cExpressionSearched.
-- @LookFor nvarchar(4000) Specifies the character expression OCCURS() searches for @cSearchExpression.



fn_Strip , fn_StripAll -> CHRTRAN()
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN( ) translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCD', 'ABC', 'YZ') -- Displays YZD
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in functions of Visual FoxPro

CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256))

returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint
select @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

while @i <= @len
select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN,
cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) ,
@i = @i + 1

return @cExpressionSearched
end

Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/25/2005 :  03:17:34  Show Profile  Reply with Quote
To be honest they are what inspired me to post here as I already had my code which as I said is not pretty or perfect but does what I need


steve

A sarcasm detector, what a great idea.
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/25/2005 :  03:18:30  Show Profile  Reply with Quote
To be honest they are what inspired me to post here as I already had my code which as I said is not pretty or perfect but does what I need


steve

A sarcasm detector, what a great idea.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000