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

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

Excellent! Thanks very much!!