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)

Share

Share

Rate

1.8 (5)