Edit string in SQL

Question

Currently I have a column in my SQL table that saves payment information. For example a column might have "VISA-2435 exp:12/13 Auth#32423". I want to edit the VISA-2435 to display instead VISA-XXXX. Each row occurs is different so I can't do a simple search and replace for that static string.

I tried the following query, but i'm not taking into account how the string might be different

 UPDATE messages 
 SET message = REPLACE(message, LIKE'%Visa-2334%', 'VISA-xxxx')
 WHERE message LIKE '%Visa'

Also, I could changed my mind and just edit the exp:12/13 portion of the string instead.

Does anybody have any suggestions?

why don't you use substring from the column to get only a number of characters? — Shoaib Shaikh
– Shoaib Shaikh, Commented Jan 1, 2014 at 18:09
i'm afraid that if I use a substring I would need to provide a fixed starting position. My strings are not all equal in length. — vlad
– vlad, Commented Jan 1, 2014 at 18:15
please share some sample data... you may be able to use regex to do so — Shoaib Shaikh
– Shoaib Shaikh, Commented Jan 1, 2014 at 18:34
This is a sample inside the column: "VISA-2435 exp:12/13 Auth#32423" and yes they are each separated by spaces. — vlad
– vlad, Commented Jan 1, 2014 at 18:50

CRAFTY DBA · Accepted Answer · 2014-01-02 13:48:27Z

1

Use a table value function (TVF) or CLR function. I think the TVF might be faster but you will have to test.

Here is a quick implementation of a TVF. It masks all numbers. From a privacy standpoint, that is best!

--
-- Create a table value function
--

CREATE FUNCTION MaskNumbers (@input_txt varchar(128))
RETURNS TABLE
AS
RETURN 
(
 SElECT 
   replace(
   replace(
   replace(
   replace(
   replace(
   replace(
   replace(
   replace(
   replace(
   replace(@input_txt, '0', 'X')
     , '1', 'X')
     , '2', 'X')
     , '3', 'X')
     , '4', 'X')
     , '5', 'X')
     , '6', 'X')
     , '7', 'X')
     , '8', 'X')
     , '9', 'X')
  as masked_txt
);

Sample call using your data.

declare @sample varchar(128) = 'VISA-2435 exp:12/13 Auth#32423'
select * from MaskNumbers(@sample);

enter image description here

Sample call using adventure works credit card table.

use AdventureWorks2012;
go

select top 5 * from [Sales].[CreditCard] cross apply MaskNumbers(CardNumber);
go

enter image description here

If you just want to change the 4 digits after VISA, use CHARINDEX() and STUFF();

-- Raw data
declare @sample varchar(128) = 
    'Random Stuff Before vIsA-2435 ExP:12/13 AuTh#32423 Random Words After';

-- Masked data
select 
    @sample as old_sample,
    case
        when charindex('visa-', @sample) > 0 then
            stuff(@sample, charindex('visa-', @sample) + 5, 4, 'XXXX')
        else @sample
    end as new_sample
go

enter image description here

edited Jan 2, 2014 at 13:48

answered Jan 1, 2014 at 21:49

CRAFTY DBA

15k4 gold badges28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jaques Over a year ago

The problem that I have with this answer is that the OP doesn't want to replace all numbers, only the numbers of the card, so this answer is not what the OP is looking for at all

Indi · Accepted Answer · 2014-01-01 22:13:31Z

0

You can use Stuff() function to do the job:

Sample table and data:

Create table table1 (val varchar(50))
Insert into table1 (val) values ('VISA-2435 exp:12/13 Auth#32423')

Query 1:

--To replace 4 numbers after VISA-
Select stuff(val, 6, 4, 'XXXX') col1
From Table1;

Query 2:

--To replace numbers after VISA- and exp:
Select stuff(stuff(val, 6, 4, 'XXXX'), 15,5,'YY/MM') col1
From Table1

Fiddle demo

answered Jan 1, 2014 at 22:13

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Comments

Amir Keshavarz · Accepted Answer · 2014-01-02 07:38:40Z

0

This code might be a solution :

 UPDATE messages 
 SET message = STUFF(@S,CHARINDEX('-',messages),(PATINDEX('% exp%',messages)-CHARINDEX('-',messages)),'XXXX')
 WHERE message LIKE '%Visa'

edited Jan 2, 2014 at 7:38

answered Jan 1, 2014 at 20:16

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

2 Comments

Jaques Over a year ago

Would be my answer as well. might just change the PATINDEX part to PATINDEX('% exp%', Lower(messages)) to make sure that the correct space is picked up.

Amir Keshavarz Over a year ago

Thanks it got ok now.

Collectives™ on Stack Overflow

Edit string in SQL

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related