seperate data in 1 row

Question

seperate data in 1 row

Krasavita

SSC-Insane

Points: 23138
More actions
October 25, 2018 at 10:24 am

#416328

Hello
HOw can I separate this data
I have buch of emails in a row, I need to keep just one and remove the rest
example:
"angelina.uhi@ors.org oladi.ade@ors.org"
Result should be just 1 email:
angelina.uhi@ors.org
Thank you

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

Krasavita - Thursday, October 25, 2018 10:24 AM
Hello
HOw can I separate this data
I have buch of emails in a row, I need to keep just one and remove the rest
example:
angelina.uhi@ors.org oladi.ade@ors.org
Result should be just 1 email:
angelina.uhi@ors.org
Thank you

SELECT LEFT(emailColumn,CHARINDEX(' ',emailColumn)-1) FROM myTable

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

there is also there at the beginning and end email ", after this code they were removed but how to remove on left side.

Thank you

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Krasavita - Thursday, October 25, 2018 10:57 AM
there is also there at the beginning and end email ", after this code they were removed but how to remove on left side.
Thank you

Really? Ever hear of REPLACE?

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

it is not working for me and I am asking for help

(LEFT([Person Email],(CHARINDEX(' ',[Person Email])-1),REPLACE([Person Name] , '"', ''))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Krasavita - Thursday, October 25, 2018 11:33 AM
it is not working for me and I am asking for help
(LEFT([Person Email],(CHARINDEX(' ',[Person Email])-1),REPLACE([Person Name] , '"', ''))

This?
declare @TestString varchar(64) = '"angelina.uhi@ors.org oladi.ade@ors.org"';

select @TestString , left(@TestString,charindex(' ',@TestString)) , replace(left(@TestString,charindex(' ',@TestString)),'"',''); go

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

Hello

Thank you so much:
so it works for this:
"bassam.nasser@crs.org julia.leis@crs.org"
t doesn't work for this:

bass.nasr@rs.org julia.liii@rs.org

Can you please help?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Krasavita - Monday, October 29, 2018 11:52 AM
Hello
Thank you so much:
so it works for this:
"bassam.nasser@crs.org julia.leis@crs.org"
t doesn't work for this:
bass.nasr@rs.org julia.liii@rs.org
Can you please help?

What do you mean it doesn't work? You do realize we can't see what you see and you didn't post anything to work with so anything we given, if tested, is tested based on what we know not what you are dealing with.

You have been here long enough to know that to get good answers you need to post DDL, sample data, and expected results.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

And FYI, my testing code seems to work just fine:
declare @TestString varchar(64) = '"angelina.uhi@ors.org oladi.ade@ors.org"';

select @TestString , left(@TestString,charindex(' ',@TestString)) , replace(left(@TestString,charindex(' ',@TestString)),'"',''); go

declare @TestString varchar(64) = 'angelina.uhi@ors.org oladi.ade@ors.org';

select @TestString , left(@TestString,charindex(' ',@TestString)) , replace(left(@TestString,charindex(' ',@TestString)),'"',''); go

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

Krasavita - Thursday, October 25, 2018 10:24 AM
Hello
HOw can I separate this data
I have buch of emails in a row, I need to keep just one and remove the rest
example:
"angelina.uhi@ors.org oladi.ade@ors.org"
Result should be just 1 email:
angelina.uhi@ors.org
Thank you

It would appear that you only want the first email address regardless of how many email addresses there may be. If that is correct, the following will work.
--===== Create and populate a test table. -- This is not a part of the solution. CREATE TABLE #TestTable (TestString VARCHAR(64)) ; INSERT INTO #TestTable (TestString) VALUES ('"angelina.uhi@ors.org oladi.ade@ors.org"') ,('bass.nasr@rs.org julia.liii@rs.org') ,('ladi.ade@ors.org bass.nasr@rs.org julia.liii@rs.org') ,('"homer.simpson@hs.org"') ; --===== Solve the given problem SELECT TestString --just for verification ,CleanString = REPLACE(SUBSTRING(TestString,1,ISNULL(NULLIF(CHARINDEX(' ',TestString),0),64)),'"','') FROM #TestTable ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Krasavita SSC-Insane Points: 23138 More actions · Answer 10

I found problem in email it goes like that:'%@rs.org
%'
eamplee:
( N'ba@rs.org
julia.leis@rs.org' )
think split needs to be used by not sure i

how can this be separated for only one email? example end result should be:ba@rs.org

thank you so much

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

Krasavita - Tuesday, October 30, 2018 7:09 AM
I found problem in email it goes like that:'%@rs.org
%'
eamplee:
( N'ba@rs.org
julia.leis@rs.org' )
how can this be separated for only one email? example end result should be:ba@rs.org
thank you so much

Are you saying the data appears on two separate lines as you have it above?

Also, you said nothing about this being NVARCHAR in your original post. What are your language settings for the database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

If you want to stop going back and forth with incremental questions, please take the time to post the code to create a table and populate it as I did in my example. It'll also help a whole lot with the language barrier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Krasavita SSC-Insane Points: 23138 More actions · Answer 13

Yes separate line for this particular case, I think string_split need to be used by I don' know how.
My code:
SELECT

    CASE
--need help here-
    WHEN a.[Person Email] like '@rs.org
%'THEN
LTRIM(RTRIM(STRING_SPLIT.a.[Person Email].value(' ', 'VARCHAR(100)'))) --- I need help here

WHEN a.[Person Email] LIKE '%@rs.iorg%' THEN REPLACE([Person Email] , '@rs.iorg', 'rs.org')

     WHEN a.[Person Email] LIKE '%.uF%' THEN REPLACE(REPLACE([Person Email] , '.uF', ''), '', '')
      WHEN a.[Person Email] LIKE '%@xs.org%' THEN REPLACE([Person Email] , '@xs.org', '@rs.org')

     ELSE
     a.[Person Email]
     END AS [Person Email],
    a.[Person Email] AS [Person Email_old]

FROM R1_Person_10032018

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

Thanks for your code but can you please do as I asked and provide the sample data in a readily consumable format as I did in my example? If you don't understand what I did, please read the article at the first link in my signature line below. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)