June 23, 2006 at 1:28 pm
i have a few strings:
"My Product 5.5CT"
"Your product 5CT"
"His product 12.5CT"
Im trying to extract the numerical value only. Can i use Patindex to remove all of the letters?
June 23, 2006 at 2:22 pm
Patindex() will only locate the position of the numerics within the string.
In order to strip the numbers out, you need to use 1 of the functions that actually modifies the string, or returns portions of it, including Replace(), Left(), Right() and Substring().
Partial example, showing functions to strip leading non-numerics:
Declare @test-2 varchar(20)
Set @test-2 = 'My Product 5.5CT'
--Position of 1st numeric
Select patindex('%[0-9]%', @test-2)
-- Strip up to 1st numeric
Select Substring( @test-2, patindex('%[0-9]%', @test-2), DataLength(@Test))
June 23, 2006 at 3:03 pm
Sho' 'nuf! You can play around with this...
CREATE
FUNCTION dbo.udf_stripString
(
@prmString VARCHAR(512) = NULL,
@charList VARCHAR(256) = NULL,
@funcAction bit = 1
)
RETURNS VARCHAR(512)
AS
/*
Name: udf_stripString
Author: Arturo T. de Lamerens
Version: MS SQL Server 2000
Function: Strips unwanted characters from a string
Strategy: When @funcAction = :
0 - Allow characters in @charList in @prmString
1 = Remove @charList characters from @prmString
@charList is a literal string of characters allowed/removed from @prmString
'-' may be used within @charList to specify a range of ordinals
'A-Za-z0-9_' would allow/remove alphabetic, numeric and the underscore characters.
Usage: udf_stripString(searchString, characterList, action)
Returns: identifierName stripped of invalid characters or NULL if invalid overall
Issues: The empty string in NVARCHAR types is handled as a single blank unless preceded by N (i.e. N'')
Revisions: 2002.04.29 ATL - Initial Version.
*/
BEGIN
DECLARE
@patPrefix VARCHAR(3),
@strPos INT
IF @funcAction = 1
SET @patPrefix = '%[' -- Strip characters in @charList from @prmString
ELSE
SET @patPrefix = '%[^' -- Allow only characters from @charList in @prmString
SET @strPos = PATINDEX(@patPrefix + @charList + ']%', @prmString)
WHILE @strPos > 0
BEGIN
SET @prmString = STUFF(@prmString, @strPos, 1, '') -- overwrite character with ''
SET @strPos = PATINDEX(@patPrefix + @charList + ']%', @prmString) -- find next character
END
RETURN @prmString
END
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply