Function fnGetIntFromString - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Function fnGetIntFromString

Author

Topic

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-11-05 : 21:59:46

Hello

I'm looking for an SQL function where I pass it a string and it extracts me the integers in that string?

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-05 : 22:14:11

fnFilterString

KH
[spoiler]Time is always against us[/spoiler]

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-11-06 : 00:09:16

excellent.

this is the code if anyone needs.

[CODE]
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Function [dbo].[fnGetINTFromString]
(
@String varchar(100)
)
RETURNS INT

/*
Extracts the integer from a string

Parameters
----------
@String String to Search
@Result Integers of search criteria

Testing
-------
PRINT dbo.fnGetINTFromString('INDI5494584OANSDSAI48844')

*/

AS
BEGIN

DECLARE @result VARCHAR(100)

SET @result=''
SELECT
@result=@result+case WHEN number like '[0-9]' THEN number ELSE '' END FROM
(
SELECT substring(@String,number,1) AS number FROM
(
SELECT number FROM master..spt_values WHERE type='p' AND number BETWEEN 1 AND LEN(@String)
) AS t
) AS t

RETURN @result
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources