Copy Column based on query result

Debilon 431 Reputation points
2022-03-17T17:21:04.67+00:00
  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  

  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,206 Reputation points
    2022-03-17T18:46:01.14+00:00

    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      |  
    +----+----------------------------------------------+-----------+--------+  
    

2 additional answers

Sort by: Most helpful
  1. Bert Zhou-msft 3,421 Reputation points
    2022-03-18T02:58:38.263+00:00

    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.

    0 comments No comments

  2. Naomi 7,361 Reputation points
    2022-03-18T20:24:57.15+00:00

    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