- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

I have a function in Access 2010 that works to extract my numbers and decimal from a text field and I need to create an SQL function. I am very new to SQL and need assistance learning how to convert the language. Thank you Missy Option Compare Database Public Function getDigits(myField) Dim i As Long, x As String, s As String If Len(Trim(Nz(myField, ""))) = 0 Then getDigits = myField Exit Function End If s = "" For i = 1 To Len(myField) x = Mid(myField, i, 1) If (x >= "0" And x <= "9") Or x = "." Then s = s & x End If Next i getDigits = s End Function

sqlstringconvertvb
Comment

First, I want to point out that your function (and my SQL version) will make 1 number out of any combination of number digits. In other words, "ab123cd456efg.789hijk" would return '123456.789'. I kept the return value as a string the way you had it, but I presume you'd want to return a number. I will show you the direct translation to a scalar function, but scalar functions are not the most efficient. It would be better to use a table-valued function, but I wanted to post this for you now while I work on that. Also, in the TVF version, I will change from the looping (for loop in your VB code; WHILE loop in my T-SQL) to use a numbers (or tally) table. Scalar function translation: CREATE FUNCTION dbo.getDigitsScalar ( @myField varchar(max) ) RETURNS varchar(max) AS BEGIN; DECLARE @i int, @x varchar(max), @s varchar(max); IF LEN(LTRIM(RTRIM(ISNULL(@myField, '')))) = 0 SET @s = @myField; ELSE BEGIN; SET @s = ''; SET @i = 1; WHILE @i = '0' AND @x

Had a bit of fun playing with this one. This isn't a function, but an example of how you could extract numbers from strings. Couple of assumptions : Numbers are delimited in the string by any other character than a numeric or a decimal point Decimal numbers less than 1 start with a '0.' declare @ATable table (id int, somedata varchar(1000)); insert into @ATable select 1,'this has no numbers in it'; insert into @ATable select 2,'this has only 1 number in it'; insert into @ATable select 3,'this has a decimal (12.456) number in it'; insert into @ATable select 4,'this has lots of 1 56 78 999 numbers in it'; insert into @ATable select 5,'this is crazy 34.8 76567567 0.003'; with Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) , tally_cte as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ) , prep as (select id, somedata + ' ' as somedata from @ATable) select A.somedata, substring(A.somedata,numericstart, numericend-numericstart) from @ATable A join ( select ID, min(N) as numericstart, N+patindex('%[^0-9.]%', substring(somedata,N,len(somedata)))-1 as numericend from tally_cte join prep on N<=len(somedata) where substring(somedata,N,1) in ('1','2','3','4','5','6','7','8','9','0') group by ID, N+patindex('%[^0-9.]%', substring(somedata,N,len(somedata)))-1 ) posns on
posns.id =
A.id order by
A.id, posns.numericstart returns somedata --------------------------------------------------- -------------- this has only 1 number in it 1 this has a decimal (12.456) number in it 12.456 this has lots of 1 56 78 999 numbers in it 1 this has lots of 1 56 78 999 numbers in it 56 this has lots of 1 56 78 999 numbers in it 78 this has lots of 1 56 78 999 numbers in it 999 this is crazy 34.8 76567567 0.003 34.8 this is crazy 34.8 76567567 0.003 76567567 this is crazy 34.8 76567567 0.003 0.003

**23** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy