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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 UDFs GETWORDCOUNT, GETWORDNUM
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Igor2004
More clever than you

Canada
78 Posts

Posted - 10/06/2007 :  17:39:59  Show Profile  Visit Igor2004's Homepage  Reply with Quote
-- Author:  Igor Nikiforov
 -- GETNUMWORD() User-Defined Function Returns the index position of a word in a string.
 -- GETNUMWORD(@cString, @cWord[, @cDelimiters] [, @nOccurrence] [, @nFlags])
 -- Return Value smallint 
 -- Returns the index position of a word in a string.
 -- If @cString don't contain the word cWord, GETNUMWORD( ) returns 0.
 -- This function may be very useful to know if a word exists in a string or not.
 -- Parameters
 -- @cString nvarchar(4000) - Specifies the string to be evaluated
 -- @cWord nvarchar(4000) - Specifies the word to search for in cString.
 -- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in cString.
 -- The default delimiters are space, tab, carriage return, and line feed.
 -- Note that GETNUMWORD( ) uses each of the characters in cDelimiters as individual delimiters, not the entire string as a single delimiter.
 -- @nOccurrence Specifies which occurrence, first, second, third, and so on, of cWord to search for in cString.
 -- By default, GETNUMWORD( ) searches for the first occurrence of cWord (nOccurrence = 1).
 -- @nFlags  Specifies Case-sensitive search criteria to apply to this function. Valid values are 0 (the default) and 1.
 -- 0 Case-sensitive search
 -- 1 Case insensitive search
 --  select dbo.GETNUMWORD('O Canada! Our home and native land!', 'Canada!', default, 1, 0)  --  Displays 2
 -- Sometime the search for substring from a string will return the incorrect result,
 -- it is necessary to search for the word in the string.
 -- declare @lcAdmissibleCodes nvarchar(4000), @lcCodeforChecking  nvarchar(4000)
 -- set @lcAdmissibleCodes = 'W,WN,IR,IU'
 -- set @lcCodeforChecking = 'I'
 -- select charindex(@lcCodeforChecking, @lcAdmissibleCodes)     -- Displays 6
 -- select dbo.GETNUMWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default, default) -- Displays  0
 -- set @lcCodeforChecking = 'IR,'
 -- select charindex(@lcCodeforChecking, @lcAdmissibleCodes)      -- Displays 6
 -- select dbo.GETNUMWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), default, default) -- Displays  0
 -- set @lcAdmissibleCodes = 'W,WN,IR,IU,WN,DS'
 -- set @lcCodeforChecking = 'WN'
 -- select dbo.GETNUMWORD(@lcAdmissibleCodes, @lcCodeforChecking, ',' + Space(1), 2, default) -- Displays  5
 -- See Also GETWORDNUM(), GETWORDCOUNT(), GETALLWORDS() User-Defined Functions  
CREATE function [dbo].[GETNUMWORD]
 (@cString nvarchar(4000), @cWord nvarchar(4000), @cDelimiters nvarchar(256) = NULL, @nOccurrence smallint = 1,  @nFlags bit = 0 )
returns smallint 
as
 begin
   declare @k smallint, @nEndString smallint, @nLenWord smallint, @wordcount smallint,
           @BegOfWord smallint,  @lnOccurrence smallint, @exit bit, @comparison bit 

   select  @BegOfWord = 1,  @lnOccurrence = 0, @exit = 0,  @cString =  isnull(@cString, ''),
           @k = 1, @wordcount = 0, @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
           @nEndString = 1 + datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end), -- for unicode,
           @nLenWord =  datalength(@cWord)/(case SQL_VARIANT_PROPERTY(@cWord,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode


 while 1 > 0
   begin
       if (@k > @BegOfWord)  -- BegOfWord begin of previous word
         begin  
           select  @wordcount = @wordcount + 1
	   if ( @nLenWord  = (@k-@BegOfWord)) -- length of previous word
              begin
		 if @nFlags > 0  --  Case insensitive search
                    select @comparison = charindex(lower(substring(@cString, @BegOfWord, @nLenWord)) COLLATE Latin1_General_BIN, lower(@cWord) COLLATE Latin1_General_BIN)
                 else --  Case-sensitive search
                    select @comparison = charindex(substring(@cString, @BegOfWord, @nLenWord) COLLATE Latin1_General_BIN, @cWord COLLATE Latin1_General_BIN)
		 if  @comparison = 1 
                     begin  
			   select  @lnOccurrence = @lnOccurrence + 1
			   if @lnOccurrence = @nOccurrence 
			       return @wordcount
		     end
		 end 
	      end 
       if  @exit  = 1 
            break
       while charindex(substring(@cString, @k, 1)  COLLATE Latin1_General_BIN,  @cDelimiters COLLATE Latin1_General_BIN) > 0  and  @nEndString > @k  -- skip  break characters, if any
            select @k = @k + 1 
       select  @BegOfWord  = @k 
       while charindex(substring(@cString, @k, 1)  COLLATE Latin1_General_BIN,  @cDelimiters COLLATE Latin1_General_BIN) = 0  and  @nEndString > @k  -- skip  the character in the word
             select  @k = @k + 1 
        if  @k >= @nEndString 
            select  @exit = 1
    end 

   return 0
 end
GO
---------------------------------------------------------------------------------------------
Ladies and Gentlemen,

I am pleased to offer, free of charge, the following string functions Transact-SQL:

AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by AT() is case-sensitive. AT similar to the Oracle function INSTR.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RAT() is case-sensitive.
ATC(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by ATC() is case-insensitive. ATC similar to the Oracle function INSTR.
RATC(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RATC() is case-insensitive.
AT2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by AT2() is case-sensitive. AT2 similar to the Oracle function INSTR.
ATC2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by ATC2() is case-insensitive. ATC similar to the Oracle function INSTR.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). OCCURS2 is faster than OCCURS.
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADL similar to the Oracle function LPAD.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADR similar to the Oracle function RPAD.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
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 similar to the Oracle function TRANSLATE.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters to help refine searches.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETNUMWORD(): Returns the index position of a word in a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).

AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.

More than 23 000 people have already downloaded my functions. I hope you will find them useful as well.

For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?2,54,33,27115

http://udfunctions.blogspot.com/
Go to Top of Page

Robert Taylor
Starting Member

2 Posts

Posted - 06/19/2015 :  23:30:55  Show Profile  Reply with Quote
Warrant by justice of the peace44. <a href="http://www.cialispharmaciefr24.com/cialis-crise-cardiaque">cialis crise cardiaque</a> cialis crise cardiaque http://www.cialispharmaciefr24.com/cialis-crise-cardiaque cialis crise cardiaque <a href="http://www.cialispharmaciefr24.com/cialis-prix-moyen">cialis prix moyen</a> cialis prix moyen http://www.cialispharmaciefr24.com/cialis-prix-moyen cialis prix moyen <a href="http://www.cialispharmaciefr24.com/wwwachat-cialis">www.achat cialis</a> www.achat cialis http://www.cialispharmaciefr24.com/wwwachat-cialis www.achat cialis
Go to Top of Page

Robert Taylor
Starting Member

2 Posts

Posted - 06/20/2015 :  00:09:04  Show Profile  Reply with Quote
Applied relaxation includes how to use muscle relaxation, when you are feeling worried or anxious, or in anxious situations. <a href="http://www.cialispharmaciefr24.com/cialis-livraison-express-france">cialis livraison express france</a> cialis livraison express france http://www.cialispharmaciefr24.com/cialis-livraison-express-france cialis livraison express france <a href="http://www.cialispharmaciefr24.com/cialis-et-sport">cialis et sport</a> cialis et sport http://www.cialispharmaciefr24.com/cialis-et-sport cialis et sport <a href="http://www.cialispharmaciefr24.com/cialis-en-suisse-pas-cher">cialis en suisse pas cher</a> cialis en suisse pas cher http://www.cialispharmaciefr24.com/cialis-en-suisse-pas-cher cialis en suisse pas cher
Go to Top of Page

Robert Taylor
Starting Member

2 Posts

Posted - 06/20/2015 :  00:48:03  Show Profile  Reply with Quote
Corresponding needs are Creativity, Responsibility and Autonomy. <a href="http://www.cialispharmaciefr24.com/achat-cialis-en-tunisie">achat cialis en tunisie</a> achat cialis en tunisie http://www.cialispharmaciefr24.com/achat-cialis-en-tunisie achat cialis en tunisie <a href="http://www.cialispharmaciefr24.com/cialis-absorption">cialis absorption</a> cialis absorption http://www.cialispharmaciefr24.com/cialis-absorption cialis absorption <a href="http://www.cialispharmaciefr24.com/prix-du-cialis-5mg-en-belgique">prix du cialis 5mg en belgique</a> prix du cialis 5mg en belgique http://www.cialispharmaciefr24.com/prix-du-cialis-5mg-en-belgique prix du cialis 5mg en belgique
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.04 seconds. Powered By: Snitz Forums 2000