So, someone had stored thousands of SSN numbers in a table but now they have an issue because some of them are incomplete or are not correctly formatted.
Thank you to blog.sqlauthority.com and little creativity I have come up with a SQL script that fixes this issue.
Here is the T/SQL script:
-- FUNCTION: ---
if exists (select * from sysobjects where id = object_id('GetNumericValue'))
drop function GetNumericValue
GO
/*
-----------------------------------------------------------------------------
Created by : www.my0.com
Functionality: Returns all number found in a string
Parameters: @sAlphaNumericString = data been passed to analyse
Return: Only number if found else 0 (zero)
Credits: http://blog.sqlauthority.com/
-----------------------------------------------------------------------------
*/
create FUNCTION dbo.GetNumericValue
(@sAlphaNumericString VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
-- Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
SET @intAlpha = PATINDEX('%[^0-9]%', @sAlphaNumericString)
BEGIN
-- Go into a loo until no numbers are found
WHILE @intAlpha > 0
BEGIN
-- The STUFF function inserts a string into another string.
-- -- It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
-- The string been passed and insert nothing - one character at the time
SET @sAlphaNumericString = STUFF(@sAlphaNumericString, @intAlpha, 1, '' )</pre>
-- Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
-- Move to the next character position
SET @intAlpha = PATINDEX('%[^0-9]%', @sAlphaNumericString )
END
END
-- Returns Zero ( 0 ) if null
RETURN ISNULL(@sAlphaNumericString,0)
END
GO
/* Sample SELECT: **** Use the FUNTION in a select statement **/
select
ID as OLDId,
dbo.GetNumericValue(ID) as NumOnly
from [dbo].[DOCPOOL]
where [DOCTYPE] = 'dwtest'
-- Sample TRIGGER: Use to format SSN in a INSERT Trigger
USE DWDATA
go
if exists (select * from sysobjects where id = object_id('tr_iFormatStringToSNN'))
drop trigger tr_iFormatStringToSNN
/*
-----------------------------------------------------------------------------
Created by : www.my0.com
Functionality: Find all number with an string and format in 999-99-9999 (SSN type)
Trigger type: INSERT
-----------------------------------------------------------------------------
*/
GO
CREATE TRIGGER tr_iFormatStringToSNN
ON [dbo].[DOCPOOL]
FOR INSERT
AS
DECLARE @dwdocid int,
@sStringDataID varchar(250)
-- collect data been passed
select
@dwdocid = DWDOCID,
@sStringDataID = dbo.GetNumericValue(ID)
FROM inserted
-- Logic - Only if there are 9 digits
--
if LEN(@sStringDataID) = 9
begin
update [dbo].[DOCPOOL]
set ID = STUFF( STUFF(@sStringDataID, 4, 0, '-'), 7, 0, '-')
where DWDOCID = @dwdocid;
end
go