select integer only from substring

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select integer only from substring

Author

Topic

jimmy2090
Starting Member

26 Posts

Posted - 2014-11-19 : 22:38:18

hi all,
i would like to select the integer only from the substring of parameter.

the query as below:



    DECLARE @Capacity VARCHAR(100)  
       
	BEGIN
	SET @Capacity = Substring('KK10eads-00l5b1', 2, 4)
	print  @Capacity
	END

the result i get is K10e
however, i only want 10
how to do that?

AASC
Starting Member

24 Posts

Posted - 2014-11-20 : 01:19:26

Step 1:first create a function to Get Numeric Value From Alpha Numeric String

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Step 2:Get numeric part from a substring

DECLARE @string VARCHAR(200)
SET @string='KK10eads-00l5b1'
SELECT dbo.udf_GetNumeric(SUBSTRING(@string,1, CHARINDEX('-',@string) ))

jimmy2090
Starting Member

26 Posts

Posted - 2014-11-20 : 04:52:46

thanks, it solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources