Get Numbers from Alpha Numeric String – Get Numeric Numbers Only

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

,

No comments yet.

Leave a Reply

*