July 7, 2017 at 11:13 am
Hi
I am displaying data that sometimes contains a SSN "somewhere" in the data
Is there a way to check for 9 numbers in a row or 999-99-9999 format and change or not display them ?
For example
field = "The quick brown foxes ssn is 111111111 or 111-11-1111 for your info'
I would like to display
"The quick brown foxes SSN is or for your info"
or
"The quick brown foxes ssn is xxxxxxxxx or xxx-xx-xxxx for your info'
Thanks
July 7, 2017 at 11:37 am
You can leverage the PATINDEX and SUBSTRING functions for this.
DECLARE @s-2 VARCHAR(500)= 'The quick brown foxes ssn is 111111111 or 111-11-1111 for your info';
DECLARE @pi INT;
SELECT @pi = PATINDEX ( '%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%' , @s-2 );
IF @pi > 0 SELECT @s-2 = SUBSTRING( @s-2, 1, @pi ) + 'XXX-XX-XXXX' + SUBSTRING( @s-2, @pi +12, 500 );
SELECT @pi = PATINDEX ( '%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' , @s-2 );
IF @pi > 0 SELECT @s-2 = SUBSTRING( @s-2, 1, @pi ) + 'XXXXXXXXX' + SUBSTRING( @s-2, @pi +10, 500 );
PRINT @s-2;
The quick brown foxes ssn is XXXXXXXXX or XXX-XX-XXXX for your info
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 7, 2017 at 11:51 am
Hi Eric
Thanks that's exactly what I need to do
Now I can't figure out how to add to my sp
here is a snippet
alter PROCEDURE [dbo].[PIF SEARCH]
@Search VARCHAR (100),
@StartDate DATETIME,
@EndDate DATETIME
AS
DECLARE @s-2 VARCHAR(max);
DECLARE @pi INT;
SELECT omd.Program, cc.Program_Site, pif.[services at this time?], cc.Client, cc.[View As]
, 'https://...rt/' + CAST(pif.ObjectID AS varchar(20)) AS refurl,pif.ObjectID
,cc.[Date]
--- function below works but removes all numbers
,dbo.[RemoveNumericCharacters](pif.[services at this time?]) as xx
FROM tables......
July 7, 2017 at 12:42 pm
You'll need to post T-SQL for RemoveNumericCharacters() function, that's where the coding will go.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 7, 2017 at 12:45 pm
Thanks Eric..
Sorry I suck at this so bad!! lol
/****** Object: UserDefinedFunction [dbo].[RemoveNumericCharacters] Script Date: 7/7/2017 2:44:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @NumRange as varchar(50) = '%[0-9]%'
While PatIndex(@NumRange, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')
Return @Temp
End
July 7, 2017 at 12:57 pm
This should basically be it.
ALTER Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @NumRange as varchar(1000);
SET @NumRange = '%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%';
While PatIndex(@NumRange, @Temp) > 0
BEGIN;
SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp)+1, 11, 'XXX-XX-XXXX');
END;
SET @NumRange = '%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%';
While PatIndex(@NumRange, @Temp) > 0
BEGIN;
SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp)+1, 9, 'XXXXXXXXX');
END;
RETURN @Temp
End
GO
PRINT dbo.RemoveNumericCharacters('The quick brown foxes ssn is 111111111 or 111-11-1111 for your info');
The quick brown foxes ssn is XXXXXXXXX or XXX-XX-XXXX for your info
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 7, 2017 at 1:02 pm
Hi Eric
Post was empty
July 7, 2017 at 1:28 pm
Thanks so much..
got to do a little googling on PATINDEX, it will drive me crazy if I dont understand how its all working!!
Thanks again and have a great weekend !!
July 7, 2017 at 1:28 pm
Eric's original solution did not address multiple instances of the same SSN-matching pattern but he fixed that. The only problem is when an SSN appears at the beginning or end of the string. Here's how I'd handle that (note my comments):
DECLARE @temp VARCHAR(500)=
'999999999 is a good SSN. 333333333 and 111-11-1111 are fine but not 999-99-9999';
SET @temp = 'X'+@temp+'X'; -- the handle SSN's in the front or end of the input string
WHILE PatIndex('%[^0-9]'+replicate('[0-9]',9)+'[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]'+replicate('[0-9]',9)+'%', @Temp)+1, 9, 'XXXXXXXXX')
WHILE PatIndex('%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%', @Temp) > 0
Set @Temp =
Stuff(@Temp, PatIndex('%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%', @Temp)+1, 11, 'XXX-XX-XXXX');
SELECT SUBSTRING(@temp, 2, LEN(@temp)-2); -- exclude the first and last dummy character.
-- Itzik Ben-Gan 2001
July 7, 2017 at 1:55 pm
Is there any problem if we follow the logic from the original function? Or should we only remove numbers from personal identification numbers?
ALTER Function [dbo].[RemoveNumericCharacters](
@Temp VarChar(1000)
)
Returns VarChar(1000)
AS
RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( @Temp, '1', 'X'), '2', 'X'), '3', 'X'), '4', 'X'), '5', 'X'), '6', 'X'), '7', 'X'), '8', 'X'), '9', 'X'), '0', 'X')
GO
EDIT: This would be way better if used as an inline table valued function.
July 7, 2017 at 2:04 pm
I wanted to remove only ssn , numbers formatted as 999999999 or 999-99-999
I did notice the erics function worked on some but not other
July 7, 2017 at 2:17 pm
Luis Cazares - Friday, July 7, 2017 1:55 PMIs there any problem if we follow the logic from the original function? Or should we only remove numbers from personal identification numbers?
ALTER Function [dbo].[RemoveNumericCharacters](
@Temp VarChar(1000)
)
Returns VarChar(1000)
AS
RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( @Temp, '1', 'X'), '2', 'X'), '3', 'X'), '4', 'X'), '5', 'X'), '6', 'X'), '7', 'X'), '8', 'X'), '9', 'X'), '0', 'X')
GOEDIT: This would be way better if used as an inline table valued function.
Unless I misread the requirement - the OP is only looking to replace the numbers with X's when the numbers are part of these patterns: '%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%' or this '%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
-- Itzik Ben-Gan 2001
July 7, 2017 at 2:29 pm
Thanks for all the advice
This looks like it will handle the ssn at the beginning and end
and if I wanted to "X" out numbers with 11 characters I would add
WHILE PatIndex('%[^0-9]'+replicate('[0-9]',11)+'[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]'+replicate('[0-9]',11)+'%', @Temp)+1, 11, 'XXXXXXXXXXX')
Below works with beginning and end ssn
ALTER Function [dbo].[Removessn2](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
SET @temp = 'X'+@temp+'X'; -- the handle SSN's in the front or end of the input string
WHILE PatIndex('%[^0-9]'+replicate('[0-9]',9)+'[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]'+replicate('[0-9]',9)+'%', @Temp)+1, 9, 'XXXXXXXXX')
WHILE PatIndex('%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%', @Temp) > 0
Set @Temp =
Stuff(@Temp, PatIndex('%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%', @Temp)+1, 11, 'XXX-XX-XXXX');
return SUBSTRING(@temp, 2, LEN(@temp)-2); -- exclude the first and last dummy character.
end
go
Thanks for all your help BONUS learned something new today 🙂
July 8, 2017 at 10:09 am
Alan.B - Friday, July 7, 2017 2:17 PMUnless I misread the requirement - the OP is only looking to replace the numbers with X's when the numbers are part of these patterns: '%[^0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]%' or this '%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
I know, but I saw the logic from the original function and posted something that should be more cost effective. But I have concerns as future requirements might include credit card and phone numbers. I was in a hurry and couldn't give a complete answer.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply