Copy Column based on query result

Question

1. I have created a query to find the number of words in a column
2. Based on the results i would like to copy the string to a different column.
3. What i would like to do is use the results as trigger to copy column ID to a new column named NewID
   AKA : if WordCount= 3 then NewID=ID
4. i know how to do so in VB / C# But have no idea what's the syntax in SQL.
5. I am using SQL 2019

Thank You

DECLARE @T AS TABLE  
    (  
        id int identity(1,1),  
        string varchar(100)  
    )  
      
    INSERT INTO @T VALUES  
    ('This Has 4 words.'),  
    ('One Two Three'),  
    ('I know what, you, have, done, last, summer ?'),  
    ('Is this you? ')  
      
      
    ;WITH CTE AS  
    (  
    SELECT  Id,  
            REPLACE(REPLACE(REPLACE(string, ' ', '><' -- Note that there are 2 spaces here  
                                   ), '<>', ''  
                           ), '><', ' '  
                    ) as string  
    FROM @T  
    )  
      
    SELECT id, LEN(string) - LEN(REPLACE(string, ' ', '')) + 1 as CountWords  
    FROM CTE  

  

Answer 1

Hi @Debilon ,

Here is a more reliable method to count number of words in a column.
I removed (1) question marks and (2) commas to prevent counting them as words.

It is still not clear about your desired output. Please update your question.

SQL

-- DDL and sample data population, start  
DECLARE @tbl AS TABLE (id int identity(1,1) PRIMARY KEY, string varchar(100));  
INSERT INTO @tbl VALUES  
('This Has 4 words.'),  
('One Two Three'),  
('I know what, you, have, done, last, summer ?'),  
('Is this you? ');  
-- DDL and sample data population, end  
          
DECLARE @separator CHAR(1) = SPACE(1)  
 , @removeChars CHAR(2) = '?,';  
  
SELECT t.*  
	, wordCount  
	, new_ID = IIF(wordCount=3, t.ID, NULL)  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r>' +   
    REPLACE(TRANSLATE(string,@removeChars,SPACE(LEN(@removeChars))), @separator, '</r><r>') +   
    '</r></root>' AS XML)) AS t1(c)  
CROSS APPLY (SELECT c.value('count(/root/r[text()])', 'INT')) AS t2(wordCount);  

Output

+----+----------------------------------------------+-----------+--------+  
| id |                    string                    | wordCount | new_ID |  
+----+----------------------------------------------+-----------+--------+  
|  1 | This Has 4 words.                            |         4 | NULL   |  
|  2 | One Two Three                                |         3 | 2      |  
|  3 | I know what, you, have, done, last, summer ? |         8 | NULL   |  
|  4 | Is this you?                                 |         3 | 4      |  
+----+----------------------------------------------+-----------+--------+  

Answer 2

Hi，@Debilon

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

 DECLARE @T AS TABLE  
     (  
         id int identity(1,1),  
         string varchar(100)  
     )  
          
     INSERT INTO @T VALUES  
    ('This Has 4 words.'),  
     ('One Two Three'),  
     ('I know what, you, have, done, last, summer ?'),  
     ('Is this you? ')  
  
	   
SELECT *,LEN(String) - LEN(REPLACE(String,' ', '')) wordcount , IIF(LEN(String) - LEN(REPLACE(String,' ', ''))=3, ID, NULL) as new_column  
FROM (  
SELECT ID,REPLACE(REPLACE(REPLACE(' ' + string, ' ', ' $!'), '$! ',''), '$!', '') String  
FROM @T ) A  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Just change the above select into cte, so you would be able to update, e.g.

;with cte as (SELECT t.*
      , wordCount
      --, new_ID = IIF(wordCount=3, t.ID, NULL)
      , NewString = IIF(wordCount = 2,t.name1,null) 
  FROM OwnerNames AS t
  CROSS APPLY (SELECT TRY_CAST('<root><r>' + 
      REPLACE(TRANSLATE(NAME1,@removeChars,SPACE(LEN(@removeChars))), @separator, '</r><r>') + 
      '</r></root>' AS XML)) AS t1(c)
  CROSS APPLY (SELECT c.value('count(/root/r[text()])', 'INT')) AS t2(wordCount)

)

 update cte -- 
 Set Name=newstring -- name column would be replaced now in the OwnerNames table

--to confirm
select * from OwnerNames