CODEWITHSUNDEEP
sqlsql-serverstringparsing
joseph Laurello
joseph Laurello

Reputation: 107

How to parse a column delimited with a comma?

I have the following select query (using SQL Server 2017):

SELECT PAYMENT_INFO
FROM CHECK_PAYMENT
WHERE (PAYMENT_FISCAL_DATE = '01/19/2018')
  AND (TENDER_MEDIA_NAME LIKE 'PaymentCard%')
ORDER BY VOID, TENDER_MEDIA_NAME

The output is a very long semicolon delimited string that I want to just return the first two fields.

Current output:

AmericanExpress;XXXXXXXXXXX4111;;;;41.47;8.00...............
MasterCard;XXXXXXXXXXXX7971;;;;40.22;10.00................
Visa;XXXXXXXXXXXX6302;;;;11.94;0.00....................

Desired output:

AmericanExpress;XXXXXXXXXXX4111
MasterCard;XXXXXXXXXXXX7111
Visa;XXXXXXXXXXXX6111

If possible I would like these separated into two columns.

I have looked through stack and found similar questions but have not found an answer that works or that I can figure out how to modify for my situation.

Upvotes: 1

Views: 520

Answers (3)

τεκ
τεκ

Reputation: 3114

SELECT        LEFT(PAYMENT_INFO, 
                   CHARINDEX(';', PAYMENT_INFO, 
                             CHARINDEX(';', PAYMENT_INFO) + 1))
FROM          CHECK_PAYMENT
WHERE        (PAYMENT_FISCAL_DATE = '01/19/2018')
     AND (TENDER_MEDIA_NAME LIKE 'PaymentCard%')
ORDER BY VOID, TENDER_MEDIA_NAME

Upvotes: 0

Random_User
Random_User

Reputation: 363

This will return a single column with the values separated with a semicolon: 

declare @example as table (
    exampleid int identity(1,1) not null primary key clustered
,   value_ nvarchar(255) not null
);

insert into @example (value_)

select 'AmericanExpress;XXXXXXXXXXX4111;;;;41.47;8.00...............' union all
select 'MasterCard;XXXXXXXXXXXX7971;;;;40.22;10.00................' union all
select 'Visa;XXXXXXXXXXXX6302;;;;11.94;0.00....................'

select substring(value_, 0, charindex(';', value_, charindex(';', value_) + 1)) Payment_Info
  from @example

Upvotes: 0

Nagaraj Raveendran
Nagaraj Raveendran

Reputation: 1235

Create the below function.

CREATE FUNCTION dbo.ColumnSplitter
(
  @String VARCHAR(MAX),
  @NroSubString INT,
  @Separator VARCHAR(5)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @St INT = 0, @End INT = 0, @Ret VARCHAR(MAX)
    SET @String = @String + @Separator
    WHILE CHARINDEX(@Separator, @String, @End + 1) > 0 AND @NroSubString > 0
    BEGIN
        SET @St = @End + 1
        SET @End = CHARINDEX(@Separator, @String, @End + 1)
        SET @NroSubString = @NroSubString - 1
    END
    IF @NroSubString > 0
        SET @Ret = ''
    ELSE
        SET @Ret = SUBSTRING(@String, @St, @End - @St)
    RETURN @Ret
END
GO

Then use it in your query.

SELECT dbo.ColumnSplitter(PAYMENT_INFO, 1, ';') as FirstColumn,
       dbo.ColumnSplitter(PAYMENT_INFO, 2, ';') as SecondColumn      
FROM CHECK_PAYMENT
WHERE (PAYMENT_FISCAL_DATE = '01/19/2018')
  AND (TENDER_MEDIA_NAME LIKE 'PaymentCard%')

Upvotes: 1

Related Questions