SplitWordList

-
ALTER function [dbo].[SplitWordList] (  @list varchar(MAX) )
returns @t table 
(  Word varchar(50) not null,  Position int identity(1,1) not null )
as begin  
declare     
@pos int,    
@lpos int,    
@item varchar(100),    
@ignore varchar(100),    
@dl int,    
@a1 int,    
@a2 int,    
@z1 int,    
@z2 int,    
@n1 int,    
@n2 int,
@n3 int,  
@c varchar(1),    
@a smallint  

select     
@a1 = ascii('a'),    
@a2 = ascii('A'),    
@z1 = ascii('z'),    
@z2 = ascii('Z'),    
@n1 = ascii('0'),    
@n2 = ascii('9'),
@n3 = ascii('-')
set @ignore = '''"'  
set @pos = 1  
set @dl = datalength(@list)  
set @lpos = 1  
set @item = ''  

while (@pos <= @dl) begin    
set @c = substring(@list, @pos, 1)    
if (@ignore not like '%' + @c + '%')
begin      
set @a = ascii(@c)      
if ((@a >= @a1) and (@a <= @z1))          
or ((@a >= @a2) and (@a <= @z2))        
or ((@a >= @n1) and (@a <= @n2))
or (@n3 = @a)      
begin        
set @item = @item + @c      
end else if (@item > '')
begin        
insert into @t values (@item)        
set @item = ''      
end    
end     
set @pos = @pos + 1   end  
if (@item > '')
begin    
insert into @t values (@item)  
end  
return
end

Comments

Post a Comment

Popular posts from this blog

Querying a Data Table Using Select Method and Lambda Expressions in Dot Net

-
Image
.Net Questions and Ans.. Q. 1 Querying a Data Table Using Select Method and Lambda Expressions in Dot Net Suppose we have a DataTable object having four fields: SSN, NAME, ADDRESS and AGE. We could create a data table and add columns in the following way: DataTable  dt =  new   DataTable (); dt.Columns.Add( "SSN" ,  typeof ( string )); dt.Columns.Add( "NAME" ,  typeof ( string )); dt.Columns.Add( "ADDR" ,  typeof ( string )); dt.Columns.Add( "AGE" ,  typeof ( int )); // Showing how to set Primary Key(s) in a Data table (Although it's not compulsory to have one) DataColumn [] keys =  new   DataColumn [1]; keys[0] = dt.Columns[0]; dt.PrimaryKey = keys; Now we store some data in our data table "dt" to show how we can perform several queries on the DataTable object like filtering some data, finding a person's record...
Read more

StringToTable

-
ALTER FUNCTION [dbo].[udf_StringToTable] (       @list NVARCHAR(MAX) ,       @delimiter NCHAR(1) = ',' --Defaults to CSV ) RETURNS @tableList TABLE ( value nvarchar(MAX) ) AS BEGIN     DECLARE @value NVARCHAR(MAX)     DECLARE @position INT     SET @list = LTRIM(RTRIM(@list))+ ','     SET @position = CHARINDEX(@delimiter, @list, 1)     IF REPLACE(@list, @delimiter, '') <> ''     BEGIN         WHILE @position > 0         BEGIN             SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))             IF @value <> ''             BEGIN                 INSERT INTO @tableList (value)   ...
Read more

GetPhoneSSNFormat

-
ALTER FUNCTION [dbo].[udf_GetPhoneSSNFormat] (     -- Add the parameters for the function here     @TypeId        INT,     @DataValue    NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN     DECLARE @FormattedValue    NVARCHAR(MAX)     SET @FormattedValue = ''     -- Will Remove all white spaces, brackets, hyphens     DECLARE @OriginalValue    NVARCHAR(MAX)     SET @OriginalValue = ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(@DataValue,'(',''),')',''),'-',''),' ',''),'')         -- Type Id : 1 For Phone #     -- Type Id : 2 For SSN #     IF @TypeId = 1         BEGIN             SELECT @FormattedValue =              ...
Read more