Split Delimiter

Question

I have two tables Table A and Table B. I need to add email id column in Table A from Table B.

For Example,

Table A

Table B

Expected Results:

SQL tag on this website is only about valid ANSI/ISO SQL standard code you clearly are using a database product here.. You should be tagging a database product like MySQL, SQL Server (MSSQL), PostgreSQL, Oracle Database... — Raymond Nijland, Jul 02 '19 at 17:06
Also see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) for help how to make better example data and expected results.. — Raymond Nijland, Jul 02 '19 at 17:07
Storing delimited data into a single column is a HUGE anti-pattern that will lead to some real pain. You may have a chance to pull this off though if your RDBMS supports splitting or arrays. What RDBMS are you using? — JNevill, Jul 02 '19 at 17:08
or to add to @JNevill string_split or regex_split ... For MySQL as example you will have to do crazy stuf -> https://stackoverflow.com/a/49756382/2548147 (post of mine) Storing Comma separated values is a non go.. — Raymond Nijland, Jul 02 '19 at 17:12
2016+ ? Then you have [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) — Raymond Nijland, Jul 02 '19 at 17:13
Also i would advice you to read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Raymond Nijland, Jul 02 '19 at 17:15
You know how to split a string from a [previous question](https://stackoverflow.com/questions/55167886/duplicate-and-split-row-based-on-value-by-using-sql-query), so make an attempt. SO is not a free code-writing service. — SMor, Jul 02 '19 at 17:38

score 0 · Accepted Answer · answered Jul 02 '19 at 18:11

try the following:

declare @table_A table (username varchar(1000))
declare @table_B table (username varchar(1000), email_id varchar(2000))

insert into @table_A
select 'User, AA # User, BB'
union
select 'User, CC # User, DD # User, EE'
union
select 'User, FF # User, GG # User, HH'

insert into @table_B
select 'User, AA','User.a@gmail.com'
union
select 'User, BB','User.b@gmail.com'
union
select 'User, CC','User.c@gmail.com'
union
select 'User, DD','User.d@gmail.com'
union
select 'User, EE','User.e@gmail.com'
union
select 'User, FF','User.f@gmail.com'
union
select 'User, GG','User.g@gmail.com'
union
select 'User, HH','User.h@gmail.com'

select A.username, B.email_id into #temp
from
(
    select username, ltrim(rtrim(value)) val
    from @table_A
    cross apply string_split(username, '#') 
)A
left join @table_B B on B.username = A.val  

select username, ltrim(rtrim(stuff((
        select DISTINCT ' # ' + u.email_id
        from #temp u
        where u.username = t.username
        for xml path('')
    ),2,1,''))) as email_id
from #temp t
group by username

drop table if exists #temp

score 0 · Answer 2 · answered Jul 02 '19 at 18:56

There is a different way using STUFF function that might be interesting

declare @TableA table (username varchar(1000))
declare @TableB table (username varchar(1000), email_id varchar(2000))

insert into @TableA values
('User, AA # User, BB'),('User, CC # User, DD # User, EE'),('User, FF # User, GG # User, HH')

insert into @TableB values
('User, AA','User.a@gmail.com'),('User, BB','User.b@gmail.com'),('User, CC','User.c@gmail.com'),
('User, DD','User.d@gmail.com'),('User, EE','User.e@gmail.com'),('User, FF','User.f@gmail.com'),
('User, GG','User.g@gmail.com'),('User, HH','User.h@gmail.com')

;with ct as (
select a.username, b.email_id
from @TableB b
    inner join @TableA a on charindex(b.username, a.username) > 0
)
select username, abc = STUFF 
(
    (
        SELECT ' # ' + email_id
        FROM ct As t2
        WHERE t2.username = t1.username
        ORDER BY username
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')
from ct t1
group by username

Result

Split Delimiter

2 Answers2