seperate data in 1 row

Question

seperate data in 1 row

Viewing 15 posts - 16 through 30 (of 42 total)

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

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

Krasavita - Tuesday, October 30, 2018 8:28 AM
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

You have been on this site for 10 years, you know what is needed to provide you with better answers and tested code. Please post working DDL, sample data, expected results in a readily consumable format.

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

Krasavita

SSC-Insane

Points: 23138

October 30, 2018 at 8:56 am

#2011706

here is sample data

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

Krasavita - Tuesday, October 30, 2018 8:56 AM
here is sample data

Where?

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

Krasavita

SSC-Insane

Points: 23138

October 30, 2018 at 9:24 am

#2011710

data was posted sorry

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

Krasavita - Tuesday, October 30, 2018 9:24 AM
data was posted sorry

Obviously you don't know what readily consumable data is. Please read the following: http://www.sqlservercentral.com/articles/Best+Practices/61537/

pietlinden SSC Guru Points: 63503 More actions · Answer 6

Nobody's going to download files that can damage his computer. Can you just post the contents of the spreadsheet here?

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

CREATE TABLE Person_oldemail ( [Person Email_old] nvarchar(100) )
INSERT INTO Person_oldemail
VALUES
( N'asfghas.opdereitem@rs.org' )
INSERT INTO Person_oldemail
VALUES
( N'augus.govoechan@rs.org' )
INSERT INTO Person_oldemail
VALUES
( N'bas.nas@rs.org
jul.le@rs.org' )
INSERT INTO Person_oldemail
VALUES
( N'bas.nas@rs.org
jul.le@rs.org' )
INSERT INTO Person_oldemail
VALUES
( N'"angel.tol@rs.org oladipo.akinmade@rs.org"' )
INSERT INTO Person_oldemail
VALUES
( N'"ang.tol@rs.org olufunbi.olaogun@rs.org"' )

INSERT INTO Person_oldemail
VALUES
( N'Am.mbe@rs.org' )

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

SELECT [Person Email_old], T.CleanedPersonEmailold, LEFT(T.CleanedPersonEmailold,CHARINDEX(' ',T.CleanedPersonEmailold)-1) SingleEmail FROM Person_oldemail CROSS APPLY(VALUES (REPLACE(REPLACE(REPLACE([Person Email_old],CHAR(13),' '),CHAR(10),' '),'"','')+' ')) T(CleanedPersonEmailold)

pietlinden SSC Guru Points: 63503 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

Which e-mail? does it matter? Use DelimitedSplit8K
SELECT oe.[Person Email_old] , e.Item , e.ItemNumber FROM Person_oldemail oe CROSS APPLY Scratchpad.dbo.DelimitedSplit8K(oe.[Person Email_old],' ') e WHERE e.ItemNumber = 1;

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

This is my full code I also have cross apply here,how can I apply yours?
SELECT       LTRIM(RTRIM(SPLIT.a.value('.', 'VARCHAR(100)'))) AS DSPN,a.DSPN AS [DSPN_old],
a.DSPNXML,a.Region,a.Country,
    CASE
                 WHEN [Person Name] LIKE '"%"' THEN REPLACE(REPLACE([Person Name] , '"', ''), '', '') ELSE [person name] END AS [Person Name],

    a.[Person Name] AS [PersonName_old],
    CASE

    WHEN a.[Person Email] LIKE '%@crs.org %' OR a.[Person Email] LIKE '"%"' THEN
REPLACE(LEFT([Person Email],CHARINDEX(' ',[Person Email])-1),'"','')


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



      WHEN a.[Person Email] LIKE '%@xrs.org%' THEN REPLACE([Person Email] , '@xrs.org', '@crs.org')
     ELSE
     a.[Person Email]
     END AS [Person Email],
    a.[Person Email] AS [Person Email_old],
    a.[Country Program]

FROM       (   SELECT CAST('<M>' + REPLACE(Corrected_DSPN, '|', '</M><M>') + '</M>' AS XML) AS DSPNXML,
                        *
                   FROM (   SELECT CASE
                                        WHEN DSPN LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') THEN DSPN
                                        WHEN DSPN LIKE ('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') THEN
                                            REPLACE(DSPN, '-', '')
                                        WHEN DSPN LIKE ('[0-9][0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]') THEN
                                            REPLACE(DSPN, '.', '')
                                        WHEN DSPN LIKE ('[0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9][0-9][0-9]') THEN
                                            REPLACE(DSPN, ' ', '')
                                        WHEN DSPN LIKE ('[0-9][0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') THEN
                                            REPLACE(DSPN, '.', '')
                                        WHEN DSPN LIKE ('[0-9][0-9][0-9][0-9]- [0-9][0-9][0-9][0-9][0-9][0-9][0-9]') THEN
                                            REPLACE(DSPN, '- .', '')
                                        WHEN DSPN LIKE ('%[0-9]%[_;]%[0-9]%') THEN
                                            translate(REPLACE(DSPN, ' ', ''), '_;', '||')
                                        WHEN dspn LIKE ('%[0-9]% %[0-9]%') THEN
                                            CASE
                                                 WHEN DSPN LIKE '"%"' THEN REPLACE(REPLACE(DSPN, '"', ''), ' ', '|')
                                                 WHEN DSPN LIKE '%-%' THEN REPLACE(REPLACE(DSPN, '-', '|'), ' ', '')
                                                 ELSE
                                                     REPLACE(
                                                         REPLACE(
                                                             REPLACE(REPLACE(REPLACE(DSPN, '.', ''), ',', ''), ' ', '|'),
                                                             '||',
                                                             '|'),
                                                         '||',
                                                         '|') END
                                        WHEN DSPN LIKE ('%[a-z]%') THEN ''
                                        WHEN LEN(DSPN) <= 11 THEN NULL
                                        WHEN DSPN LIKE ('%/%') THEN REPLACE(DSPN, '/', '|')
                                        WHEN DSPN IS NULL THEN ''
                                        ELSE '0' END Corrected_DSPN,
                                   *
                              FROM R1_Person_10032018) tst ) a
CROSS APPLY DSPNXML.nodes('/M') AS SPLIT(a)

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

Here is Krasavita's code formatted using SQLPrompt, so my be easier to read:
select 'DSPN' = ltrim(rtrim([SPLIT].[a].[value]('.', 'VARCHAR(100)'))) , 'DSPN_old' = [a].[DSPN] , [a].[DSPNXML] , [a].[Region] , [a].[Country] , 'Person Name' = case when [Person Name] like '"%"' then replace(replace([Person Name], '"', ''), '', '') else [person name] end , 'PersonName_old' = [a].[Person Name] , 'Person Email' = case when [a].[Person Email] like '%@crs.org %' or [a].[Person Email] like '"%"' then replace(left([Person Email], charindex(' ', [Person Email]) - 1), '"', '') when [a].[Person Email] like '%@crs.iorg%' then replace([Person Email], '@crs.iorg', '@crs.org') when [a].[Person Email] like '%@xrs.org%' then replace([Person Email], '@xrs.org', '@crs.org') else [a].[Person Email] end , 'Person Email_old' = [a].[Person Email] , [a].[Country Program] from ( select 'DSPNXML' = cast('<M>' + replace([Corrected_DSPN], '|', '</M><M>') + '</M>' as xml) , * from ( select case when [DSPN] like ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then [DSPN] when [DSPN] like ('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then replace([DSPN], '-', '') when [DSPN] like ('[0-9][0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]') then replace([DSPN], '.', '') when [DSPN] like ('[0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then replace([DSPN], ' ', '') when [DSPN] like ('[0-9][0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then replace([DSPN], '.', '') when [DSPN] like ('[0-9][0-9][0-9][0-9]- [0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then replace([DSPN], '- .', '') when [DSPN] like ('%[0-9]%[_;]%[0-9]%') then [translate](replace([DSPN], ' ', ''), '_;', '||') when 'dspn' like ('%[0-9]% %[0-9]%') then case when [DSPN] like '"%"' then replace(replace([DSPN], '"', ''), ' ', '|') when [DSPN] like '%-%' then replace(replace([DSPN], '-', '|'), ' ', '') else replace( replace( replace(replace(replace([DSPN], '.', ''), ',', ''), ' ', '|') , '||' , '|' ) , '||' , '|' ) end when [DSPN] like ('%[a-z]%') then '' when len([DSPN]) <= 11 then null when 'DSPN' like ('%/%') then replace([DSPN], '/', '|') when [DSPN] is null then '' else '0' end Corrected_DSPN , * from R1_Person_10032018 ) tst ) a cross apply DSPNXML.nodes('/M') as split(a);

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

Ya just gotta wonder if all those CASEd LIKEs are slower than just doing unconditional REPLACEs with the right collation.

p.s. Apparently the version of SQLPrompt you used doesn't know what the TRANSLATE function is in 2017.

--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)

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

Jeff Moden - Tuesday, October 30, 2018 6:20 PM
Ya just gotta wonder if all those CASEd LIKEs are slower than just doing unconditional REPLACEs with the right collation.
p.s. Apparently the version of SQLPrompt you used doesn't know what the TRANSLATE function is in 2017.

SQLPrompt won't change code that way. All it did was format the code that was written. Easier to read than what was originally posted.

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

Lynn Pettis - Tuesday, October 30, 2018 10:36 PM
Jeff Moden - Tuesday, October 30, 2018 6:20 PM
Ya just gotta wonder if all those CASEd LIKEs are slower than just doing unconditional REPLACEs with the right collation.
p.s. Apparently the version of SQLPrompt you used doesn't know what the TRANSLATE function is in 2017.
SQLPrompt won't change code that way. All it did was format the code that was written. Easier to read than what was originally posted.

But it did. It put brackets around the word TRANSLATE.

--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)

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

Jeff Moden - Wednesday, October 31, 2018 6:06 AM
Lynn Pettis - Tuesday, October 30, 2018 10:36 PM
Jeff Moden - Tuesday, October 30, 2018 6:20 PM
Ya just gotta wonder if all those CASEd LIKEs are slower than just doing unconditional REPLACEs with the right collation.
p.s. Apparently the version of SQLPrompt you used doesn't know what the TRANSLATE function is in 2017.
SQLPrompt won't change code that way. All it did was format the code that was written. Easier to read than what was originally posted.
But it did. It put brackets around the word TRANSLATE.

Oh, I see. I am running an older version of SQLPrompt. Company would not renew the Toolbelt Essentials license. I thought you were talking about actually changing the code to use TRANSLATE. I should have looked closer. Sorry.