SQL存储过程结果集中记录的排列 [英] Permutations of records in SQL Store Procedure resultset

问题描述

您好，



我的桌子上有一个全名专栏，如RUA Fernando DE

我需要的是获得结果将所有排列和个人单词组合。



全名我作为商店程序的输入提供：RUA Fernando DE

我需要表格中的所有记录，如全名。



RUA Fernando DE

Fernando DE RUA

Fernando RUA DE

DE RUA Fernando

DE Fernando RUA

RUA DE Fernando



需要SQL程序来实现这一点。



提前谢谢。

Hello,

I have a table with a column for full name like "RUA Fernando DE"
What i need is to get result will all the permutation and combination of individuals words.

Full Name I provide as Input to a store procedure: "RUA Fernando DE"
I need all records from the table having full name like.

RUA Fernando DE
Fernando DE RUA
Fernando RUA DE
DE RUA Fernando
DE Fernando RUA
RUA DE Fernando

Need SQL Procedure to achieve this.

Thanks in advance.

推荐答案

下面的过程将帮助您从表中找出与给定输入中单词的可能组合相匹配的所有列值。根据需要使用您的表名和列名修改在sql-procedure底部写入的select查询。

Hi,

Below procedure will help you in finding out all the column values from a table which matches the possible combinations of the words from the given input. Modify the select query written at the bottom of sql-procedure with your table name and column name as required.

CREATE PROCEDURE spCombinations ( @inputStr VARCHAR(100))
AS BEGIN
DECLARE @ValueStr  VARCHAR(100)
DECLARE @Count  INT, @Loop INT = 1, @totalSum INT
DECLARE @Query1  VARCHAR(1000),@Query2  VARCHAR(1000),@Query3  VARCHAR(1000),
@Query4  VARCHAR(1000), @Query5  VARCHAR(1000), @Query6  VARCHAR(1000),
@Query  VARCHAR(4000),@Combination VARCHAR(1000)
--Temporary table to capture all the words separately
CREATE TABLE #tmpvalues      
(   
	intIndex INT IDENTITY(1,1),
	intProc INT,
	subStr VARCHAR(100)
)
--Temporary table to store all the possible combinations
CREATE TABLE #tmpCombinations
(   
	subCombStr VARCHAR(1000)      
)  
--get the sub-strings(words) from input statement into a temp table
WHILE LEN(@inputStr) > 0
BEGIN
    SET @ValueStr = LEFT(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr) - 1, -1),
                       LEN(@inputStr)))
    SET @inputStr = SUBSTRING(@inputStr,ISNULL(NULLIF(CHARINDEX(' ', @inputStr), 0),
                       LEN(@inputStr)) + 1, LEN(@inputStr))
    INSERT INTO #tmpvalues VALUES (@Loop, @ValueStr )
    SET @Loop=@Loop+1
END
SELECT @Count = MAX(intINDEX) FROM #tmpvalues
SET @Loop=1
--Set an integer values for each words
--This will be used to filter the combinations in which any two words are repating
DECLARE @tempIntAdd INT--Addition factor
SET @tempIntAdd =@Loop*@Count
WHILE @Loop <= (@Count-1)
BEGIN
    DECLARE @tempIntProc INT
    SELECT @tempIntProc = intProc from #tmpvalues where intIndex=@Loop
    UPDATE #tmpvalues SET intProc=@tempIntProc+ @tempIntAdd WHERE intIndex = @Loop+1
    SET @Loop = @Loop+1
    SET @tempIntAdd=@tempIntAdd*2
END
--
SET @Loop=1
SET @Query1='INSERT INTO #tmpCombinations SELECT DISTINCT '
SET @Query2='ALL_COMBINATIONS FROM'
SET @Query3=' '
SET @Query4=' WHERE'
SET @Query5='('
SET @Query6=')'
-- Generate the dynamic query to get permutations and combination of individual words
WHILE @Loop <= @Count
BEGIN
	SELECT @ValueStr = subStr FROM #tmpvalues WHERE intIndex=@Loop	
	SET @Query1 = @Query1 + 'T' + CAST(@Loop AS VARCHAR) + '.subStr '
	if(@Loop<@Count)
	 SET @Query1=@Query1 + '+ '' '' + ' 
    SET @Query3= @Query3 + '#tmpvalues ' + 'T' + CAST(@Loop AS VARCHAR)
    if(@Loop<@Count)
	 SET @Query3=@Query3+ ', '
    SET @Query5=@Query5 + 'T'+ CAST(@Loop AS VARCHAR) + '.intProc'
    if(@Loop<@Count)
	 SET @Query5=@Query5+ ' + '
    SET @Loop = @Loop+1
END
SELECT @totalSum = SUM(intProc) from #tmpvalues
--Create final query
SET @Query = @Query1+@Query2+@Query3+@Query4+@Query5+@Query6 + ' =' + CAST(@totalSum AS VARCHAR)
--Execute the dynamic Query
EXECUTE(@Query)
--select the full name from table if any combinations are present, change the query if required
SELECT FullName FROM TableName WHERE FullName IN(SELECT subCombStr from  #tmpCombinations)
--Drop temporary tables
DROP TABLE #tmpCombinations
DROP TABLE #tmpvalues
END

上述程序可以用下面的脚本执行。

The above procedure can be executed with below script.

exec spCombinations 'RUA Fernando DE'

希望这会有所帮助。如果您的目的得到解决，请告诉我。

Hope this will help. Let me know if your purpose is solved.

这篇关于SQL存储过程结果集中记录的排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！