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) ))
Go to Top of Page

jimmy2090
Starting Member

26 Posts

Posted - 2014-11-20 : 04:52:46
thanks, it solved
Go to Top of Page
   

- Advertisement -