Split AgeRange col to AgeMin & AgeMax

[danb]

Hi,

Maybe someone can help...

I have a database which contains a column called AgeRange, which holds values such as 11 - 14, 7, 4 - 18, etc.

Is there a T-SQL query I could run to split this data into two new columns called AgeMin and AgeMax - using the hyphen as the divider?

There are several thousand records, so to automate this would be great!

Thanks for any help...

Dan

 

[raskew]

Dan -

Not sure how T-SQL differs, but ....

Function ySplit(), shown below, splits a string to the left or

right of the specified delimiter.

Here's how you could use an update query to populate fields

AgeMin and AgeMax (both long integers) from AgeRange (which

I assume is a text field).

UPDATE tblAges SET tblAges.AgeMax = Val(ysplit([AgeRange],"-",True))
  , tblAges.AgeMin = Val(ysplit([AgeRange],"-",False));

And the user-defined function:

Function ySplit(ByVal pTarget As String, pItem As String, _
         Optional ShowLeft As Boolean = True) As String
'*******************************************
'Purpose:    Splits a string to the left or
'            right of pItem

'Created by: raskew

'Inputs:     1)  ySplit("The quick+ brown fox", "+", True)
'            2)  ysplit("The quick+ brown fox", "+", False)
'Output:     1)  The quick
'            2)  brown fox
'*******************************************

Dim strLeft As String, strRight As String
Dim n As Integer

    n = InStr(pTarget, pItem)
    If n = 0 Then
       ySplit = pTarget
    Else
       ShowLeft = IIf(IsMissing(ShowLeft), True, ShowLeft)
       strLeft = Left(pTarget, n - 1)
       strRight = Mid(pTarget, n + 1)
       ySplit = Trim(IIf(ShowLeft, strLeft, strRight))
    End If
    
End Function

HTH - Bob

 

[danb]

Excellent! Thanks very much!!