question

Jackson_Chaiy avatar image
Jackson_Chaiy asked

Compare two value

for example we there are database one store table one that have two column ID and Key that in table store value like below: ID Key 1 Hello,Hi 2 You,Her 3 Where are u? ............ and one value store in sentence that have value like this: Hi How are You Where are you?....... my purpose is went to get value that the same in table this sentence below to take it like[ [Hi] How are [You] [Where are you?] to replace value in sentence like this please help me thanks
ssc
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you tell us a bit more about what you ultimately want to achieve? What is the business need for being able to do this? It sounds like you're trying to set up some sort of a mail-merge. If we can understand this we might be able to devise a better solution.
0 Likes 0 ·

1 Answer

·
sabinweb avatar image
sabinweb answered
I had to create a function to process this. CREATE TABLE Source_Key ([ID] INT PRIMARY KEY CLUSTERED , [Key] VARCHAR(50) NOT NULL ) INSERT INTO Source_Key([ID],[Key]) VALUES (1,'Hello,Hi') ,(2,'You,Her') ,(3,'Where are you?') CREATE TABLE Sentence ([key] VARCHAR(500)) INSERT INTO Sentence([key]) VALUES('Hi How are You Where are you?') CREATE FUNCTION [dbo].[fn_MySentence] ( @I_VC_Sentence VARCHAR(500) ) RETURNS VARCHAR(500) AS BEGIN DECLARE @O_vc_newSentence AS VARCHAR(1000) =''; WITH cte AS ( SELECT SK.ID ,CA.Data AS [Key] ,ROW_NUMBER() OVER(ORDER BY SK.ID, CA.Data) AS RN FROM Source_Key AS SK CROSS APPLY dbo.fn_Split(SK.[key],',') AS CA CROSS APPLY (SELECT @I_VC_Sentence AS [key]) AS S WHERE REPLACE(S.[key]+' ' ,CA.Data+' ','['+CAST( SK.ID AS VARCHAR(30)) +'] ') <> S.[Key] ) ,cte_Rec AS ( SELECT RN , REPLACE(@I_VC_Sentence+' ',[key]+' ','['+[key] +'] ') AS newSentence FROM cte WHERE rn = 1 UNION ALL SELECT R1.RN + 1 , REPLACE(R1.newSentence+' ',[key]+' ','['+[key] +'] ') FROM cte_Rec AS R1 INNER JOIN cte AS R2 ON R1.RN = R2.RN - 1 ) SELECT @O_vc_newSentence = MIN(newSentence) FROM cte_Rec; RETURN @O_vc_newSentence; END Then , the final select: SELECT S.[key] AS OldSentence ,dbo.fn_MySentence(S.[key]) AS NewSentence FROM dbo.Sentence AS S The output: OldSentence NewSentence Hi How are You Where are you? [Hi] How are [You] [Where are you?]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.