Technical Article

Find all actual words from Dictionary Table using jumbled word

,

  1. Create UDF as it is in script to split a word into charecters.
  2. Execute rest of the statement in order to get result
  3. 🙂
USE [AdventureWorks]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TFN_Word_To_Charecter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TFN_Word_To_Charecter]
GO
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[TFN_Word_To_Charecter](
    @word VARCHAR(8000) -- List of delimited items
) RETURNS @List TABLE (Charecters VARCHAR(8000))
BEGIN
DECLARE @Result TABLE ( 
   C char(1)
) 
DECLARE @N INT 
SET @N = 1  
WHILE @N <= LEN(@word) BEGIN 
    INSERT @Result ( 
        C 
    ) VALUES ( 
        SUBSTRING(@word, @N, 1) 
    ) 
    SET @N = @N + 1 
END
insert @List select c from @Result 
RETURN
END
GO
select * into #dictionary from(
values  ('ghost'),('andriod'),('font'),('adobe'),('sing'),('Van'),('microsoft'))X(word)
DECLARE @word VARCHAR(2000)='abcdefgh&efongr'--'abcdefghijklmnopqrstuvwxyz'
SELECT WORD FROM #dictionary
EXCEPT(
		SELECT distinct WORD FROM
			(
				SELECT * FROM 
					(
						SELECT Word,CAST(B.Charecters AS VARCHAR(200))AS CH --,C.Charecters as SearchedWord 
							FROM
								(
									SELECT  WORD FROM #dictionary --ORDER By NEWID ()
								)A
						CROSS APPLY (
										SELECT Charecters FROM dbo.[TFN_Word_To_Charecter](a.Word)
									)B
					)E LEFT JOIN 
						( 
							SELECT Charecters FROM ( 
													SELECT Charecters FROM dbo.[TFN_Word_To_Charecter](@word)
												   )D
						)C  ON C.Charecters=E.CH
			)FINAL WHERE Charecters is  null
	 )
	 
DROP TABLE #dictionary

Rate

1.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

1.8 (5)

You rated this post out of 5. Change rating