Replacing a character depending on what surrounds it (SQL query question)

[Remster]

Folks

The following is a simplified version of a puzzle I'm faced with, but if I can solve the simplified version I'll be able to solve the real thing.

Take the following column:

MyField
1-2A-B


YZ-A6
53-75-XC

How within a query can I replace the hyphens with spaces but only where they appear between two numbers? So this is the result I'm after:

MyField
1 2A-B
YZ-A6
53 75-XC

(Please note that the letters and numbers could be any values in any positions.)

Thanks

Remster

[qa12dx]

i think this will get you started on the path...

SELECT Table2.MyField, InStr([MyField],"-") AS Expr1, Int(Mid([myfield],InStr([MyField],"-")-1,1)) AS Expr2, Int(Mid([myfield],InStr([MyField],"-")+1,1)) AS Expr3
FROM Table2
WHERE (((InStr([MyField],"-"))>0));

[Remster]

Thanks, I'll give it a go.

[ssanfu]

I couldn't solve your problem strictly with a query, but maybe this will help.

In a standard module, paste the following code:

Public Function ReplaceDash(pTheString) As String
    Dim sTemp As String
    Dim Pos As Integer
    Dim sChrBefore As String
    Dim sChrAfter As String

    sTemp = Trim(pTheString)

    Pos = InStr(1, [sTemp], "-")
    If Pos > 0 Then
        Do Until Pos = 0
            
            'get char before and after the dash
            sChrBefore = Mid([sTemp], Pos - 1, 1)
            sChrAfter = Mid([sTemp], Pos + 1, 1)
            
            'check to see if numbers
            If IsNumeric(sChrBefore) And IsNumeric(sChrAfter) Then
            'both numbers, replace dash with space
                sTemp = Left(sTemp, Pos - 1) & " " & Mid(sTemp, Pos + 1)
            End If
            
            'do it again
            Pos = InStr(Pos + 1, [sTemp], "-")
        Loop
    End If
    
    ReplaceDash = sTemp

End Function

The query is

SELECT Table2.MyField, ReplaceDash([myfield]) AS [No Dash] FROM Table2;

Change the blue text to your table/field names