Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hello,
I have only 2 columns in sql server. The first column (name) starts with the name "abc" in the first line and it ends in the 4th line (Endabc). I need to have second red color column: (while the first column starts with "abc" until the word "Endabc", update second column and put 'abc' in all those cells <big>until the word "Endabc</big>"(and doing the same for "def" in the line 5 and 6)
How can i do it?
Thanks.

<a href="https://drive.google.com/file/d/1bkEVIB-nYbtbH7iQq1GAWwoN89OeMmzZ/view?usp=sharing"></a>


&>

What I have tried:

What i wrote is like it but it is not correct because i must use sth lie do while for this!

<pre>create table Test3 (name nvarchar(255), SecondColumn nvarchar(255))

INSERT INTO Test3 values ('abc', '')
 INSERT INTO Test3 values ('123abc456', '')
 INSERT INTO Test3 values ('', '')
  INSERT INTO Test3 values ('Acode', '')
 INSERT INTO Test3 values ('Endabc', '')
 INSERT INTO Test3 values ('def', '')
 INSERT INTO Test3 values ('Enddef', '')
-----------------------

Update Test3
SET  SecondColumn  = 'abc'  
FROM Test3
where [name]  LIKE '%abc%'

Update Test3
SET  SecondColumn  = 'def'  
FROM Test3
where [name]  LIKE '%def%'

select *
from Test3
Posted
Updated 7-Jul-20 1:37am

1 solution

Your data does not have a sequence defined. Without a column to order by, there is no defined order for your rows, so there is no way to know which rows should appear between "abc" and "endabc".

Add a column to provide a sequence to your table:
SQL
CREATE TABLE Test3
(
    ID int NOT NULL IDENTITY(1, 1),
    name nvarchar(255) NOT NULL,
    SecondColumn nvarchar(255) NULL
);

INSERT INTO Test3 (name)
VALUES
    ('abc'), 
    ('123abc456'), 
    (''), 
    ('acode'), 
    ('endabc'), 
    ('def'), 
    ('enddef')
;
You can then use a sub-query to find the name group for each row:
SQL
SELECT 
    ID, 
    name,
    (
        SELECT TOP 1 Substring(name, 4, Len(name))
        FROM Test3 As T2 
        WHERE T2.ID >= T.ID
        And T2.name Like 'end%'
        ORDER BY T2.ID
    ) As NameGroup
FROM 
    Test3 As T
;

/*
ID  name       NameGroup
------------------------
1   abc        abc
2   123abc456  abc
3              abc
4   acode      abc
5   endabc     abc
6   def        def
7   enddef     def
*/
You can then use that to update your table:
SQL
UPDATE
    T
SET
    SecondColumn = (
        SELECT TOP 1 Substring(name, 4, Len(name))
        FROM Test3 As T2 
        WHERE T2.ID >= T.ID
        And T2.name Like 'end%'
        ORDER BY T2.ID
    )
FROM 
    Test3 As T
;
 
Share this answer
 
Comments
Member 10943256 7-Jul-20 8:44am    
Perfect! Thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900