Convert Simple VBA to T SQL Function

Question

Convert Simple VBA to T SQL Function

mbrady5

SSCrazy

Points: 2835
More actions
April 18, 2014 at 7:36 am

#306610

Hello, I am an idiot when it comes to VBA. With that being said, I could use some help. There is some VBA I'd like to convert to a T SQL function and I am certain there are many people that could do such a thing quickly.
Thanks for any assistance.
Public Function GetType(strNumber As String) As String
Dim intPos
For intPos = 1 To Len(strNumber)
If IsNumeric(Mid(strNumber, intPos, 1)) Then
Exit For
End If
Next
GetType = Trim(Left(strNumber, intPos - 1))
End Function

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mbrady5 (4/18/2014)
Hello, I am an idiot when it comes to VBA. With that being said, I could use some help. There is some VBA I'd like to convert to a T SQL function and I am certain there are many people that could do such a thing quickly.
Thanks for any assistance.
Public Function GetType(strNumber As String) As String
Dim intPos
For intPos = 1 To Len(strNumber)
If IsNumeric(Mid(strNumber, intPos, 1)) Then
Exit For
End If
Next
GetType = Trim(Left(strNumber, intPos - 1))
End Function

Does this just return the first x characters before it encounters a number?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbrady5 SSCrazy Points: 2835 More actions · Answer 2

That is exactly what I am hoping to do.

Thank you for the quick response

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You could do this a number of ways. You had a function from VBA. The direct translation there would be a scalar function. Those do not perform very well in t-sql.

If you need a scalar function you could do it along these lines.

declare @MyString varchar(20) = 'abc3def'

select left(@MyString, case when patindex('%[0-9]%', @MyString) = 0 then LEN(@MyString) else patindex('%[0-9]%', @MyString) - 1 end)

However, since in t-sql we rarely deal with one row at a time it would be better to do this as a set.

with MyValues as

(

select 'abc123def' as SomeValue union all

select '123abc' union all

select 'abcdef' union all

select 'ab12'

)

select left(SomeValue, case when patindex('%[0-9]%', SomeValue) = 0 then LEN(SomeValue) else patindex('%[0-9]%', SomeValue) - 1 end)

from MyValues

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mbrady5 SSCrazy Points: 2835 More actions · Answer 4

mbrady5

SSCrazy

Points: 2835

April 18, 2014 at 8:07 am

#1707078

Thank you so much. This did the trick

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You're welcome. Glad that worked for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/