asked
How to convert VB to Sql Function GetDigits
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
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
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
Dim OrderID As Long Dim InvoiceID As Long OrderID = Nz(Me![Order ID], 0) ' Gracefully exit if invoice already created If CustomerOrders.IsInvoiced(OrderID) Then If MsgBoxYesNo(OrderAlreadyInvoiced) Then CustomerOrders.PrintInvoice OrderID End If ElseIf ValidateOrder(Invoiced_CustomerOrder) Then ' Create Invoice Record If CustomerOrders.CreateInvoice(OrderID, 0, InvoiceID) Then ' Mark all Order Items Invoiced ' Need to change Inventory Status to SOLD from HOLD Dim rsw As New RecordsetWrapper With rsw.GetRecordsetClone(Me.sbfOrderDetails.Form.Recordset) While Not .EOF If Not IsNull(![Inventory ID]) And ![Status ID] = OnHold_OrderItemStatus Then rsw.Edit ![Status ID] = Invoiced_OrderItemStatus rsw.Update Inventory.HoldToSold ![Inventory ID] End If rsw.MoveNext Wend End With ' Print the Invoice CustomerOrders.PrintInvoice OrderID SetFormState
23 People are following this question.
