Find all actual words from Dictionary Table using jumbled word
- Create UDF as it is in script to split a word into charecters.
- Execute rest of the statement in order to get result
- 🙂
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