Find chars in any order in Sql Server

cubaman_24
cubaman_24 used Ask the Experts™
on
Hello:
I have a datatable in sql server 2005 containing words in spanish. Given a char array, I would like to find all the words which contains those characters, no mather in wich order they appear. For example, "avi" shoul match "avion" and also "iva". Is it possible?
Thanks for your attention.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

You need to use regular expression to do that.
SQL Server 2005 support CLR integration, where you can create a regular expression function in visual studio, integrate it to SQL Server, and then call the function from sql query.

Follow the procedure from one of these :
http://blogs.msdn.com/b/sqlclr/archive/2005/06/29/regex.aspx
http://justgeeks.blogspot.co.uk/2008/08/adding-regular-expressions-regex-to-sql.html


The query that you will run will looks like :

select * from tablename where 1 = dbo.RegexMatch('[avi]');
Commented:
This link also may be helpful :
http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server-2005-2008

And the query above should be :
select * from tablename where 1 = dbo.RegexMatch(fieldName,'[avi]');

It passes the fieldName as parameter to the RegexMatch function.
'here's a stored procedure
CREATE TABLE [dbo].[WORDS](
	[WORD] [varchar](50) NULL
) ON [PRIMARY]

GO

Open in new window


EXEC	@return_value = [dbo].[CharsMatch]
		@CHARS = 'aaaamz'

Open in new window



-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE CharsMatch  
	@CHARS nvarchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
    CREATE TABLE #GASH (LETTER nvarchar(1));
    CREATE TABLE #WORDRETURN (WORD nvarchar(400));
    
    DECLARE @I int;
    DECLARE @CNT int;
    DECLARE @Y int;
    DECLARE @WORDX nvarchar(400);
    DECLARE @X nvarchar(1);
    DECLARE @FLAG int;
    
    SET @I = 1;
    WHILE @I <= LEN(@CHARS)
    BEGIN
		INSERT #GASH VALUES(SUBSTRING(@CHARS, @I, 1));
		SET @I = @I + 1;
    END
    
    DECLARE C CURSOR FAST_FORWARD FOR
		SELECT  WORD FROM WORDS; 
	OPEN C;
	
	DECLARE D CURSOR FAST_FORWARD FOR
			SELECT LETTER, COUNT(0) AS CNT FROM #GASH GROUP BY LETTER;
		
	FETCH NEXT FROM C INTO @WORDX;
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @FLAG = 1;
	    
		OPEN D;
		FETCH NEXT FROM D INTO @X, @CNT;
		WHILE @@FETCH_STATUS = 0 AND @FLAG = 1
		BEGIN 
			SET @I = 1;
			SET @Y = 1;
			WHILE @I <= @CNT AND @FLAG = 1 
			BEGIN
				SET @Y = CHARINDEX(@X, @WORDX, @Y);
				
				IF @Y < 1 
					SET @FLAG = 0;
				SET @I = @I + 1;	
				SET @Y = @Y + 1;	
			END
			FETCH NEXT FROM D INTO @X, @CNT;
		END
	    CLOSE D;
		IF @FLAG = 1
		   INSERT #WORDRETURN VALUES(@WORDX);			 	
		
	
	FETCH NEXT FROM C INTO @WORDX;
	END
	
	CLOSE C;
	DEALLOCATE C;
				
	SELECT * FROM #WORDRETURN;
    
    
END
GO

Open in new window

Author

Commented:
@agusacil Thanks for your help!

Commented:
@cubaman_24 you're welcome. Glad that it helps.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial