Developing Matt

My Technical Journal

modify words not strings

leave a comment »

Written by Christian Mercure, I found this gem in the sqlmagazine this month.  Can I say that I love this magazine?  I don’t know that this is the best solution (clr would be more efficient), but it will come in handy without a doubt.  FUN!

USE [AdventureWorks]
GO
/****** Object:  UserDefinedFunction [dbo].[cm_ModifyWord]    Script Date: 08/09/2008 23:29:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[cm_ModifyWord]
/*
This function will replace a specific word by a new value.
It will search and replace a word (not a string).
For example, replacing bike by motorcycle will not modify the word superbike in the following examples:
Select dbo.cm_ModifyWord(‘This is a bike, not a superbike’, ‘bike’, ‘motorcycle’, ‘.,:;!’)
    , dbo.cm_ModifyWord(‘.bike, superbike bike; bike, bike: bike!’, ‘bike’, ‘motorcycle’, ‘.,:;!’)

Word can be in any position of the text (begining, middle or end).
Word can be delimited by punctuation caracters such as .,;:!

How to use this function:

Example 1: select and run the following statements
to replace word Bikes by Trucks in field [Name] of table [Sales].[Store] in database [AdventureWorks]
______________________________________________________________________________________________________________
Use [AdventureWorks]
Declare @OldWord As Varchar(255)
Declare @NewWord As Varchar(255)
Set @OldWord=’Bikes’
Set @NewWord=’Trucks’

Select [Name],len(dbo.cm_ModifyWord([Name], @OldWord, @NewWord, ‘.,:;!’))
From [Sales].[Store]
Where [Name] Like ‘%’+@OldWord+’%’

Update [Sales].[Store]
Set [Name]=dbo.cm_ModifyWord([Name], @OldWord, @NewWord, ‘.,:;!’)
Where [Name] Like ‘%’+@OldWord+’%’

Select [Name]
From [Sales].[Store]
Where [Name] Like ‘%’+@NewWord+’%’

______________________________________________________________________________________________________________
Example 2: select and run the following statements
to replace word Trucks by Bikes in field [Name] of table [Sales].[Store] in database [AdventureWorks]
______________________________________________________________________________________________________________

Use [AdventureWorks]
Declare @OldWord As Varchar(255)
Declare @NewWord As Varchar(255)
Set @OldWord=’Trucks’
Set @NewWord=’Bikes’

Select [Name],len(dbo.cm_ModifyWord([Name], @OldWord, @NewWord, ‘.,:;!’))
From [Sales].[Store]
Where [Name] Like ‘%’+@OldWord+’%’

Update [Sales].[Store]
Set [Name]=dbo.cm_ModifyWord([Name], @OldWord, @NewWord, ‘.,:;!’)
Where [Name] Like ‘%’+@OldWord+’%’

Select [Name]
From [Sales].[Store]
Where [Name] Like ‘%’+@NewWord+’%’

*/
(
    @InputString As Varchar(Max)            — string to test
    , @WordToReplace As Varchar(Max)        — word to find and modify
    , @ReplacementValue As Varchar(Max)        — new value of found word
    , @Punctuation As Varchar(Max)            — caracters used for punctuation (example: ,.:;!) if null or empty function will use ‘.’
)
RETURNS Varchar(Max)
AS
BEGIN

Declare @ModifiedString As Varchar(Max)
Declare @PunctuationLenght As Int
Declare @PunctuationPosition As Int
Declare @PunctuationCaracter As Varchar(1)

— get value of inputstring to be modified
Set @ModifiedString=@InputString

— if list of punctuation caracters is null or empty set it to ‘.’
If @Punctuation Is Null Or @Punctuation=” Or @Punctuation=Space(1) Set @Punctuation=’.’

— lenght of punctuation list
Set @PunctuationLenght=Len(@Punctuation)

— temporary replace actual punctuation so searched word will be delimited by spaces
Set @PunctuationPosition=1
While @PunctuationPosition<=@PunctuationLenght
    Begin
        — get value of actual punctuation in list
        Set @PunctuationCaracter=SUBSTRING ( @Punctuation ,@PunctuationPosition , 1 )

        Set @ModifiedString=
                            Replace(
                                        Space(1) + @ModifiedString + Space(1)
                                        , @PunctuationCaracter
                                        , Space(1) + @PunctuationCaracter + Space(1)
                                    )
        Set @PunctuationPosition=@PunctuationPosition+1
    End

    — replace all found words with new value
        Set @ModifiedString=
                            LTRIM(
                                RTRIM(
                                    Replace(
                                            Space(1) + @ModifiedString + Space(1)
                                            , Space(1) + @WordToReplace + Space(1)
                                            , Space(1) + @ReplacementValue + Space(1)
                                            )
                                    )
                            )

— reset previous punctuation values
Set @PunctuationPosition=1
While @PunctuationPosition<=@PunctuationLenght
    Begin
        — get value of actual punctuation in list
        Set @PunctuationCaracter=SUBSTRING ( @Punctuation ,@PunctuationPosition , 1 )
        Set @ModifiedString=
                            LTRIM(
                                RTRIM(
                                        Replace(
                                            Space(1) + @ModifiedString + Space(1)
                                            , Space(1) + @PunctuationCaracter + Space(1)
                                            , @PunctuationCaracter
                                        )
                                      )
                                 )
        Set @PunctuationPosition=@PunctuationPosition+1
    End

— return modified string
Return @ModifiedString

End

Advertisements

Written by matt

November 27, 2008 at 5:13 pm

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: