March 6, 2017 at 5:59 am
Hi,
I have a requirement where I have to tranform an existing multi-select picklist column.
The source data exists in the folllowing format:
Source
3G Contact::C.E.O Contact::External Newsletter Subscriber::GSMA Press::Interconnection Contact::Speaker Contact
There are a set number of target values in the picklist and the delimiter of the target db is a single semicolon.
Can anyone please suggest a way in which I can transform the delimiter and write a statement which checks the string for any of the source values and then maps that to it's target value?
Thanks in advance!
Chris
March 6, 2017 at 6:14 am
Sounds a little like a homework problem, so I'll ask what have you tried? Have you considered using the REPLACE function? As you've provided information only about a single input, and no information about the rest of the problem other than having to check this string for a particular value, I'd have to make far too many assumptions to provide any useful assistance. How about you look up the REPLACE function and post back what you've tried and we'll help you from there?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 6, 2017 at 6:16 am
aldous.chris - Monday, March 6, 2017 5:59 AMHi,I have a requirement where I have to tranform an existing multi-select picklist column.
The source data exists in the folllowing format:
Source
3G Contact::C.E.O Contact::External Newsletter Subscriber::GSMA Press::Interconnection Contact::Speaker ContactThere are a set number of target values in the picklist and the delimiter of the target db is a single semicolon.
Can anyone please suggest a way in which I can transform the delimiter and write a statement which checks the string for any of the source values and then maps that to it's target value?
Thanks in advance!
Chris
I'm not 100% sure what you are asking for. Are you trying to replace one delimiter with another, or is there more to it than that? Perhaps you could provide the result you are expecting, based on the sample data?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 6, 2017 at 6:42 am
aldous.chris - Monday, March 6, 2017 5:59 AMHi,I have a requirement where I have to tranform an existing multi-select picklist column.
The source data exists in the folllowing format:
Source
3G Contact::C.E.O Contact::External Newsletter Subscriber::GSMA Press::Interconnection Contact::Speaker ContactThere are a set number of target values in the picklist and the delimiter of the target db is a single semicolon.
Can anyone please suggest a way in which I can transform the delimiter and write a statement which checks the string for any of the source values and then maps that to it's target value?
Thanks in advance!
Chris
1) First thing I will say is that this is not really a thing you want SQL Server to be doing. If it is a picklist for an external app then let it do the parsing, string replacement or whatever it is you are asking for.
2) Speaking of which, it sure would have helped if you had posted up what your expected output was for that given input.
3) Just in case you are looking for a rudamentary string replacement, review the REPLACE function in SQL Server Books Online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2017 at 8:17 am
Hi All,
Thank you for your quick responses. Apologies for the lack of detail in my previous post.
A bit of background. I am migrating Contact data from Eloqua to SFDC. The source column is a multi select picklist and the target column in SFDC is also multi-select picklist.
I need to do two things:
1.Replace the delimiter between the values from '::' to ';' - thank you for the suggestion of utilising the REPLACE function, that would make sense.
2. Map the values from the source column to their respective values in SFDC.
E.g. The four rows below exist in Eloqua. I would need to map each value to it's respective value in SFDC. I.e. ' 3G Contact' = 'Contact - 3G' in SFDC.
Source
1. 3G Contact::C.E.O Contact::External Newsletter Subscriber::GSMA Press::Interconnection Contact::Speaker Contact
2. 3G Contact::C.E.O Contact::External Newsletter Subscriber::Speaker Contact::Training Contact
3. 3G Contact::C.E.O Contact::GSMA Invoice::GSMA Main
4. 3G Contact::C.E.O Contact::Fraud Issues Contact::GSMA Invoice::GSMA Main::Intl Gway SS7 Service Agrm & Schd IR21::IREG Tests - IR.21::Network Manager::Non-Standard IR.21::Roaming - IR.21 Administration::Roaming Main Contact::Security Issues Contact
Target
1. Contact - 3G;C.E.O;External Newsletter Subscription; GSMA Press;NULL; Speaker
2. Contact - 3G;C.E.O;External Newsletter Subscription;Speaker;Training
etc
Is there a way I could loop through the column (948k rows) and in one statement repalce the delimiter and replace each value?
Does that make more sense?
Thanks
March 6, 2017 at 8:58 am
aldous.chris - Monday, March 6, 2017 8:17 AMHi All,Thank you for your quick responses. Apologies for the lack of detail in my previous post.
A bit of background. I am migrating Contact data from Eloqua to SFDC. The source column is a multi select picklist and the target column in SFDC is also multi-select picklist.
I need to do two things:
1.Replace the delimiter between the values from '::' to ';' - thank you for the suggestion of utilising the REPLACE function, that would make sense.
2. Map the values from the source column to their respective values in SFDC.E.g. The four rows below exist in Eloqua. I would need to map each value to it's respective value in SFDC. I.e. ' 3G Contact' = 'Contact - 3G' in SFDC.
Source
1. 3G Contact::C.E.O Contact::External Newsletter Subscriber::GSMA Press::Interconnection Contact::Speaker Contact
2. 3G Contact::C.E.O Contact::External Newsletter Subscriber::Speaker Contact::Training Contact
3. 3G Contact::C.E.O Contact::GSMA Invoice::GSMA Main
4. 3G Contact::C.E.O Contact::Fraud Issues Contact::GSMA Invoice::GSMA Main::Intl Gway SS7 Service Agrm & Schd IR21::IREG Tests - IR.21::Network Manager::Non-Standard IR.21::Roaming - IR.21 Administration::Roaming Main Contact::Security Issues ContactTarget
1. Contact - 3G;C.E.O;External Newsletter Subscription; GSMA Press;NULL; Speaker
2. Contact - 3G;C.E.O;External Newsletter Subscription;Speaker;Training
etcIs there a way I could loop through the column (948k rows) and in one statement repalce the delimiter and replace each value?
Does that make more sense?
Thanks
That helps. There should be no need for a loop on this. You can update all the rows in one pass (or do it some N rows at a time to avoid long-duration locking/blocking if this is a production system, where N is hundreds or thousands in size and you are doing some form of quick index seek to hit the rows). Just LEFT JOIN to the lookup table and do a REPLACE on the :: and a STUFF for the looked-up value. Don't have time to write the query but that should be doable in a single pass. Obviously you can do two passes if desired.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2017 at 9:23 am
Hi Kevin,
Thanks for the advice. I'll give that a shot and see where I get with it!
Cheers,
Chris
March 6, 2017 at 12:31 pm
one other option which may be viable depending on the number of distinct values of that field and its mapping to the new system.
something around these lines
define map_table with
Source_value varchar(500) -- size as needed
new_value varchar(500)
populate map_table with distinct list of source_field and its corresponding mapping on new system
insert into new_table
select ot.field1
....
, ot.fieldx --- fields as required
, coalesce(mt.new_value, ot.source_field) as source_field
from old_table ot
left outer join map_table mt -- left outer to catter for nulls
on mt.source_value = ot.source_field
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply