How to add values to an empty string, and to parse the string values using them for another task

Question

How to add values to an empty string, and to parse the string values using them for another task

minh tran 216

Good morning,
I would like to seek your help and expertise regarding to how to add values from a table to an empty string , to parse the empty string looking for a particular string value and using each of them for another task such as inserting into another table.

I have a table such as Table Names contain columns last_name, and address.

I would like to add every address values in Table Names that the last names is "John" in to a declare target string of length 200.

At the end, my target string containing values; for example, ("1 Main String, GA 30033", "2 North Avenue, Hapeville 30324", "@4 North Druid Hill, Atlanta 30129").

I then plan to parse the values in my target string , and then add it each of the value
to a address tables .

I would like to seek your help if you can show me some sample code on how to achieve the above tasks.

Many Thanks for all of the help.
Du

2 answers

Your answer

Answer 1

Viorel 122.6K

Consider one of approaches:

declare @result varchar(200) = 
    (
        select a = trim(address)
        from Names
        where last_name = 'John'
        for xml path(''), root('d')
    )

print @result

It will build a string that contains all of addresses.

Then you can split it into separate values (rows) using a query like this:

declare @x as xml = @result

select a.value('.', 'varchar(max)') as address from @x.nodes('/d/a') n(a)

minh tran 216 Reputation points

2021-12-15T15:56:05.88+00:00

Thank you so much for the help.
I am sorry that I have a question regarding adding a pair of columns values into a a string. May I ask you a question please regarding adding multiple column values into a a string ? For example , If I would like add address and last name into a declare string Result, then may I ask on how do I do it for adding a pair of values into a string?

At the end , May I ask if I would like to parse them and print them in pair , then how do I do it?

Many Thanks for all of the help.
Du

Viorel 122.6K

To include the name and address:

-- generating the string:

declare @result varchar(1000) = 
    (
        select last_name as n,
               address as a
        from Names
        where last_name = 'John'
        for xml path('i'), root('d')
    )

print @result

-- parsing

declare @x as xml = @result

select a.value('n[1]', 'varchar(max)') as last_name, 
       a.value('a[1]', 'varchar(max)') as address 
from @x.nodes('/d/i') n(a)

minh tran 216

Thank you so much for the great solution.
May I ask one more question related to terminate a session id ? I have the code working for terminating a session id and I want to embedded in the code that you gave me but I do not the correct way to do it.

 -- generating the string:

 declare @result varchar(1000) = 
     (
         select last_name as n,
                address as a,
                id as session_id
         from Names
         where last_name = 'John'
         for xml path('i'), root('d')
     )

 print @result

 -- parsing

 declare @x as xml = @result

 select a.value('n[1]', 'varchar(max)') as last_name, 
        a.value('a[1]', 'varchar(max)') as address,
        a.value('session_id[1]', 'varchar(max)') as address


 from @x.nodes('/d/i') n(a)

If I have the piece of code terminating session_id as below:

 USE [master];

 DECLARE @kill varchar(8000) = '';  
 SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
 FROM sys.dm_exec_sessions
 WHERE database_id  = 8

 EXEC(@kill);

My Question is that how do I embed teh code of terminating session_id into your code so that it would terminate each of my session_id given that I have more than one session_ids?

Many Thanks,
Du

Viorel 122.6K

To insert the @killoton value to the list of names and addresses:

-- generating the string  
  
declare @result varchar(300) =   
    (  
        select  
            k,  
            last_name as n,  
            address as a  
        from (values (@kill)) d(k), Names i  
        where last_name = 'John'  
        for xml auto, elements  
    )  
  
print @result  
  
-- parsing  
  
declare @x as xml = @result  
  
declare @kill varchar(max) = @x.value('(/d/k)[1]', 'varchar(max)')  
  
select a.value('n[1]', 'varchar(max)') as last_name,   
       a.value('a[1]', 'varchar(max)') as address   
from @x.nodes('/d/i') n(a)

minh tran 216 Reputation points

2021-12-18T00:31:28.78+00:00
I am so sorry that I get lost, and do not understand the statement

from (values (@kill)) d(k), Names i

May I ask what does it do ? and how it is related and can be replaced the statement sbelow since I understand the below statements

DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id = 8

I apologize for asking basic question , and I am very thankful for all of the help.

Du
Viorel 122.6K Reputation points

2021-12-18T08:40:35.44+00:00

The part ‘(values (@killoton )) d(k)’ creates an imaginary table called d that contain a single column k and a single row containing the value of the @killoton . It is then combined with Names table. The final XML will contain a single element containing @killoton and a series of names and addresses.

If you are interested in new issues related to sessions, which do not seem related to the original problem of names and addresses, I think that you can start separate questions, giving new details.

Answer 2

Hi @minh tran

Maybe you want to merge data from multiple column into one column ，right？
Here is an example，hope it helps

create table sourcetable  
( id integer identity(1,1),  
last_name varchar(20),  
  address varchar(50))  
  
insert into sourcetable(last_name,address)  
select 'joe','Main String, GA 30033'  
union all  
select 'John','North Avenue, Hapeville 30324'  
union all  
select 'John','North Druid Hill, Atlanta 30129'  
  
select * from sourcetable  
  
select CONCAT('name:',last_name,'   ','address:',address) from sourcetable where last_name = 'John'

And if you want to merge all row values ，you can try this statement

DECLARE @CHARSTRING VARCHAR(MAX)  
SET @CHARSTRING =  ';WITH CTE AS(select CONTENT =  CONCAT(''name:'',last_name,''   '',''address:'',address) from sourcetable where last_name = ''John'')  
(SELECT STRING_AGG(CONTENT,''||'') AS SSS FROM CTE)'  
EXEC (@CHARSTRING)

Best Regards,
Isabella

If the answer is the right solution, please click "Accept Answer" and 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.

Share via

How to add values to an empty string, and to parse the string values using them for another task

2 answers

Your answer