Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert Simple VBA to T SQL Function Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:26 AM
Points: 97, Visits: 504
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

Post #1563029
Posted Friday, April 18, 2014 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 13,325, Visits: 12,810
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563031
Posted Friday, April 18, 2014 7:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:26 AM
Points: 97, Visits: 504
That is exactly what I am hoping to do.

Thank you for the quick response
Post #1563035
Posted Friday, April 18, 2014 8:01 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 13,325, Visits: 12,810
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563039
Posted Friday, April 18, 2014 8:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:26 AM
Points: 97, Visits: 504
Thank you so much. This did the trick
Post #1563040
Posted Friday, April 18, 2014 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 13,325, Visits: 12,810
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563041
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse