-2

sql database email_column(1 column) has value : test@gmail.com,test1@gmail.com

I have a stored procedure where i should get the email column values into 2 separate columns(primary_email,secondary_email)

email_column test@gmail.com,test1@gmail.com

Desired output: primary_email secondary_email

test@gmail.com test1@gmail.com

kate
  • 11
  • 2
  • [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 Jun 14 '19 at 14:51
  • 1
    dear goodness. change your db design please. having a comma separated list is a hack. – granadaCoder Jun 14 '19 at 14:53
  • 1
    This is a legitimate ask. Sometimes we're stuck with legacy problems and have to either work around them or fix them. Separating these values, preferably into multiple discrete data, is part of fixing the problem (e.g. to migrate to better schema). – Dave Skender Jun 14 '19 at 16:57

1 Answers1

0

Create a simple helper parsing function that you can use inline in your select statement.

CREATE FUNCTION dbo.udf_Parse(
  @InputString      VARCHAR(1000)
 ,@Separator        CHAR(1)
 ,@PositionInString TINYINT
) 
RETURNS VARCHAR(250)
AS
BEGIN

    DECLARE @OutputString VARCHAR(250)

    DECLARE @StringList TABLE (
      ID  INT IDENTITY(1,1)
     ,VAL VARCHAR(250)
    )

    INSERT INTO @StringList (VAL)
    SELECT value 
    FROM STRING_SPLIT(@InputString,@Separator)

    SELECT @OutputString = VAL 
    FROM @StringList 
    WHERE ID=@PositionInString

    RETURN @OutputString
END
GO

Then, you can use it like this:

-- create test data
IF OBJECT_ID('tempdb..#tmpOriginal','U') IS NOT NULL
DROP TABLE #tmpOriginal

CREATE TABLE #tmpOriginal (
  email_column VARCHAR(50)
)

INSERT INTO #tmpOriginal(email_column) 
  VALUES ('test@gmail.com,test1@gmail.com')
        ,('foo@email.com,bar@email.com')
-- (end test data)


-- how to use:
-- (you can put this in your stored procedure if you want)

SELECT 
  dbo.udf_Parse(email_column,',',1) AS Email1
 ,dbo.udf_Parse(email_column,',',2) AS Email2
 ,dbo.udf_Parse(email_column,',',3) AS Email3
FROM #tmpOriginal


-- cleanup test data
DROP TABLE #tmpOriginal

Produces:

Email1         | Email2          | Email3
-----------------------------------------
test@gmail.com | test1@gmail.com | NULL
foo@email.com  | bar@email.com   | NULL

With all that said, having CSV in a single column is a problematic design. And, using this parsing function is an expensive process that will be slow on large data-sets. However, with this function, you can use as is or to parse and migrate into a better schema.

Dave Skender
  • 611
  • 5
  • 11