Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 stringy

Author  Topic 

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-20 : 03:31:19
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

3279 Posts

Posted - 2005-05-20 : 14:20:37
quote:
WHILE LEN(@restString) > 0 -- This may be a bug

HA HA HA !!!
Gotcha!

PS. Nice though

rockmoose
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-23 : 06:36:56
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

3279 Posts

Posted - 2005-05-23 : 07:25:46
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

78 Posts

Posted - 2005-05-25 : 01:51:31
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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-25 : 03:17:34
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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-25 : 03:18:30
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
   

- Advertisement -