Need assistance to remove numbers from a Column result

Question

Post reply

Need assistance to remove numbers from a Column result

chef423

SSCertifiable

Points: 5278
More actions
September 16, 2024 at 2:15 am

#4457792
I have a Colum that outputs this data, depending on the Card Type:
0991719957291436|02|22|VISA|Visa|
if 'VISA' concat 12 #'s from left to right
--I only need the numbers '1436' I also need to remove everything after the 1436, so 12 digits before the 1436
7956459769292596|04|21|M/C|MasterCard|
if 'MasterCard' concat 12 #'s from left to right
--Same here, IF 'MasterCard' then remove first 12, then remove else after the 6 before the |05
195779235276008|09|20|AMEX|Amex|
if 'Amex' concat 11 #'s from left to right
--Same idea. But with Amex, I only need to remove the first 11 digits, then everything after.
(These 'card' numbers are fictitious, FYI)
Here is the Code I am working with:
This this line I need to edit correctly: ELSE isnull(t.s_Result, '')
USE [someDB]
GO
/****** Object: StoredProcedure [dbo].[dw_daily_user_cc_detail] Script Date: 9/15/2024 7:07:37 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dw_daily_user_cc_detail]
(
@dtstart_time datetime,
@dtend_time datetime,
@user_id int
)
AS
SET NOCOUNT ON
select
CASE t.s_credit_tran_type
WHEN 'external' THEN 'XTNL'
ELSE ISNULL(t.s_credit_card_type, 'XTNL')
END as cardtype,
CASE t.s_credit_tran_type
WHEN 'external' THEN ' '
ELSE isnull(t.s_Result, '')
--ELSE isnull(t.s_card_last_lour, '')
END as card,
isnull(t.c_amount, 0) + isnull(ttips.c_amount, 0) as payment,
isnull(tips.c_amount, 0) - isnull(ttips.c_amount, 0)as tip,
isnull(t.c_amount, 0) + isnull(tips.c_amount, 0) as total
FROM
tips right outer join transactions t on tips.i_tip_transaction_id = t.i_transaction_id
left outer join transactions ttips on t.i_transaction_id = ttips.i_transaction_parent_trans_id
WHERE
t.s_credit_tran_type in ('external', 'post', 'sale') AND
t.b_cancel = 0 and
isnull(ttips.b_cancel, 0) = 0 AND
isnull(ttips.s_credit_tran_type, 'tip') = 'tip' AND
t.i_user_id = @user_id AND
t.i_ticket_id IN
(select i_ticket_id from Ticket
where
dt_close_time >= @dtstart_time and
dt_close_time < @dtend_time
)
ORDER BY
isnull(t.c_amount, 0) DESC

THANKS TO ANYONE WHO CAN SHED SOME LIGHT. I have tried RTRIM and CONCAT but I cannot get it to work correctly.
Best,
Chef
- This topic was modified 1 month ago by chef423.
September 16, 2024 at 8:40 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246908 More actions · Answer 1

You should know how to post consumable data by now, with >5,000 points.

We can't run your code unless you provide supporting DDL. But you should know that too.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18329 More actions · Answer 2

Setup some test data

CREATE TABLE TestCardData (
    s_Result NVARCHAR(255),
    s_credit_card_type NVARCHAR(50),
    s_credit_tran_type NVARCHAR(50),
    c_amount DECIMAL(18, 2)
);


INSERT INTO TestCardData (s_Result, s_credit_card_type, s_credit_tran_type, c_amount)
VALUES
('0991719957291436|02|22|VISA|Visa|', 'Visa', 'post', 100.00),          -- VISA
('7956459769292596|04|21|M/C|MasterCard|', 'MasterCard', 'post', 200.00), -- MasterCard
('195779235276008|09|20|AMEX|Amex|', 'Amex', 'post', 300.00),             -- AMEX
('1234567890123456|01|23|VISA|Visa|', 'Visa', 'sale', 150.00),            -- VISA
('8765432198765432|05|21|M/C|MasterCard|', 'MasterCard', 'external', 250.00), -- MasterCard
('789456123456789|03|24|AMEX|Amex|', 'Amex', 'sale', 350.00);             -- AMEX

Query Using REPLACE

SELECT s_credit_card_type AS cardtype,
       CASE WHEN CHARINDEX('VISA', s_Result) > 0 THEN REPLACE(SUBSTRING(s_Result, 1, CHARINDEX('|', s_Result)), LEFT(s_Result, 12), '')
            WHEN CHARINDEX('MasterCard', s_Result) > 0 THEN REPLACE(SUBSTRING(s_Result, 1, CHARINDEX('|', s_Result)), LEFT(s_Result, 12), '')
            WHEN CHARINDEX('AMEX', s_Result) > 0 THEN REPLACE(SUBSTRING(s_Result, 1, CHARINDEX('|', s_Result)), LEFT(s_Result, 11), '')
            ELSE ISNULL(s_Result, '')
       END AS card,
       c_amount AS payment
  FROM TestCardData
;

Query Using SUBSTRING + CHARINDEX

SELECT s_credit_card_type AS cardtype,
       CASE WHEN CHARINDEX('VISA', s_Result) > 0 THEN SUBSTRING(s_Result, 13, 4)
            WHEN CHARINDEX('MasterCard', s_Result) > 0 THEN SUBSTRING(s_Result, 13, 4)
            WHEN CHARINDEX('AMEX', s_Result) > 0 THEN SUBSTRING(s_Result, 12, 4)
            ELSE ISNULL(s_Result, '')
       END AS card,
       c_amount AS payment
  FROM TestCardData
;

Screenshot 2024-09-16 120723

ScottPletcher SSC Guru Points: 101059 More actions · Answer 3

I would use STUFF for this, as most custom-fitted to do this task:

SELECT s_Result,
    STUFF(s_Result, 1, CASE s_credit_card_type 
        WHEN 'Amex' THEN 11 WHEN 'MasterCard' THEN 12 
        WHEN 'Visa' THEN 12 ELSE 0 END, '') AS s_Result_adjusted
FROM TestCardData;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90296 More actions · Answer 4

If all you need is the last 4 from the card number - I would use this:

RIGHT(LEFT(s_Result, CHARINDEX('|', s_Result) - 1)), 4) AS last_four

No need to determine what type of card or worry about how many characters exist prior to the last four.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004241 More actions · Answer 5

First (trying to save your career here), if this column isn't encrypted with a lengthy salt, someone will beat on you soon. Hopefully it will be an auditor and not someone telling you that you've been hacked.

Jeffrey Williams is spot on with his code. I've not tested it for performance but, in a similar fashion, the following code will also do the trick and might be a little faster on big stuff because it only uses 2 string functions instead of 3.

 SELECT SUBSTRING(s_Result,CHARINDEX('|',s_Result)-4,4)
   FROM dbo.TestCardData
;

If you need all the pieces, see the "Resources" section of the following article for a text splitter that will do the job quite nicely for you.

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

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

chef423 SSCertifiable Points: 5278 More actions · Answer 6

Thank you to everyone. And to Phil, you're right, but its been a good while since I posted. I will follow your lead in the future.

Again, thanks for all the responses. I really appreciate it.

Chef