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

quote:

---

WHILE LEN(@restString) > 0 -- This may be a bug

---