SQL Replace

[Banaticus]

Can more complex statements be put in Replace()? For instance, say:
replace all / or . with ^ in someString
or
replace 67 with 59 in someString unless there's a 4 after the seven
replace 67 with 59 unless it's 674, then don't touch it
replace 67[!4] with 59?

 

[WayneRyan]

Banaticus,

Numbers or strings?

If you use strings you can certainly:

IIf(Instr(1, strNumber, "674") = 0, Replace(strNumber, "67", "59"), strNumber)

But with a lot of combinations (nested IIfs), it get ugly pretty quick.

Without any specifics I won't take this any further. Is it possible that
you could seperate those data elements?

Wayne

 

[DB7]

As Wayne said Banaticus.
In case it is numbers, in order to use Wayne's method, maybe;
strNumber = CStr(txtNumber), then ...

I use the Array() often, for long winded, repetitive coding...

Dim varSearch As Variant, varReplace As Variant

varSearch = Array("674","67","/",".","~")
varReplace = Array("674","59","\","^","`")

For x = 0 to UBound(varSearch)
If Instr(strNumber,varSearch(x))>0 Then
strNumber = Replace(strNumber,varSearch(x),varReplace(x))
End If
Next x

...something like that.

Ps. Replace by default, should change all occurences.
I found it doesn't always.
you can in the Count Argument of Replace(), use Len(strNumber)

 

[Banaticus]

In a SQL query, I something like (going from memory):
SELECT *
FROM [App Info]
ORDER BY Replace(Nz([App Info].[Last Name]), ' ', ''), Replace(Nz([App Info].[First Name]), ' ', '')


I'd essentially like to use a Regular Expression in the SQL Replace, like:
ORDER BY Replace(String, /[^\w]|\d|[_]/, '')
i.e., order the list by looking solely at the letters in the name, nothing else.

So, O'Neal becomes ONeal, Byman-Furgin becomes BymanFurgin, de Vino becomes deVino, etc.

 

[DB7]

I'd create a function, along the lines that Wayne or I, described above.
Use this function for one of your fields. then Order by that field;

SELECT txtName, txtCity, Replacing([txtName]) As Replaced FROM.....
....ORDER BY Replaced;

 

[raskew]

Hi -

See if the functions here http://www.access-programmers.co.uk/forums/showthread.php?t=97687 are of assistance.

HTH - Bob

 

[Banaticus]

I'm not passing in the string to be replaced ByVal as I don't want to change the original string in the database -- if I was going to pass it ByVal, then I'd change this from a Function to a Sub. I'm also using one function, I think it will be slightly faster this way.

What do you mean, "Use this function for one of your fields"? How do I call a VB function from a SQL query? If I call a function, don't I have to use the word Call?

SELECT *
FROM [App Info]
ORDER BY CharacterReplacer(Nz([App Info].[Last Name]), ' ', '-', ''''), CharacterReplacer(Nz([App Info].[First Name]), ' ', '-', '''');

The following code is based off Raskew's code:

Function CharacterReplacer(aString as String, ParamArray characterList() As Variant) As String
	Dim upperBound as Integer
	Dim counter as Integer
	Dim location as Integer

	upperBound = UBound(characterList)
	For counter = 0 to upperBound
		location = InStr(aString, characterList(counter))
		If location > 0 Then
			Dim leftSide as String
			Dim rightSide as String
			leftSide = Left(aString, location - 1)
			rightSide = Mid(aString, location + 1)
			CharacterReplacer = leftSide + rightSide
		Else
			CharacterReplacer = aString
		End If
	Next counter
End Function

This is what I have in my code currently:

Private Sub GoGoNamesComboBox(WhichName As String)
'MsgBox ("GoGoNamesComboBox begins")
    Dim stringSELECT As String
    Dim stringWHEREfirst As String
    Dim stringWHEREsecond As String
    Dim stringORDERBY As String

    stringWHEREsecond = ""
    stringSELECT = "SELECT [App Info].[Soc Sec #] as [Soc Sec], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name FROM [App Info]"
    Select Case WhichName
        Case "All"
            stringWHEREfirst = ""
            If Me.ShowCanceledTerminated = False Then 'ShowCanceledTerminated is a checkbox on the form
                stringWHEREsecond = " WHERE [App Info].[App Type] <> 'Canceled/Terminated'"
            End If
        Case "AB"
            stringWHEREfirst = " WHERE ([App Info].[Last Name] aLike '[AB]%')"
            If Me.ShowCanceledTerminated = False Then 'ShowCanceledTerminated is a checkbox on the form
                stringWHEREsecond = " AND [App Info].[App Type] <> 'Canceled/Terminated'"
            End If
'...
    End Select
    stringORDERBY = " ORDER BY CharacterReplacer(Nz([App Info].[Last Name]), ' ', '-', ''''), CharacterReplacer(Nz([App Info].[First Name]), ' ', '-', '''');"
    'stringORDERBY = " ORDER BY REPLACE(Nz([App Info].[Last Name]),' ',''), REPLACE(Nz([App Info].[First Name]),' ',''), REPLACE(Nz([App Info].MA),' ','');"
    Me.Names_Combo_Box.RowSource = stringSELECT & stringWHEREfirst & stringWHEREsecond & stringORDERBY

    'Now that I've changed the RowSource, reload the thing
    Me.Names_Combo_Box.SetFocus
    Me.Names_Combo_Box.Requery
'...
    Me.Names_Combo_Box.Dropdown
'MsgBox ("GoGoNamesComboBox ends")
End Sub
Function CharacterReplacer(aString As String, ParamArray characterList() As Variant) As String
    Dim upperBound As Integer
    Dim counter As Integer
    Dim Location As Integer

    upperBound = UBound(characterList)
    For counter = 0 To upperBound
        Location = InStr(aString, characterList(counter))
        If Location > 0 Then
            Dim leftSide As String
            Dim rightSide As String
            leftSide = Left(aString, Location - 1)
            rightSide = Mid(aString, Location + 1)
            CharacterReplacer = leftSide + rightSide
        Else
            CharacterReplacer = aString
        End If
    Next counter
End Function

 

[DB7]

Take a look at my example, I called it "Replaced".
Use it the tyhe way one uses any function, in a query

...WHERE Sum(txtSales) > 100....

SELECT txtName, Date() As txtDate, ....

...ORDER BY IIF(txtNumber Is Null,txtSale, txtNumber)....

 

[Banaticus]

I need to display the names as they are. O'Neal needs to appear as O'Neal, not as ONeal. But, those non-letter characters need to be ignored as far as sorting. So, the names should be sorted like this:
ONeal, Barry
O'Neal, Jack
ONeal, William
Jack should fall between Barry and Willam and the apostrophe in Jack's last name should be ignored when the names are sorted (ORDER BY). So, I need the SELECT to pull the name exactly as it is.

SELECT [App Info].[Soc Sec #] as [Soc Sec], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name

Since I'm ordering the names while ignoring those non-letter characters, I need to call the function in the ORDER BY section of the SQL statement. The SQL statement is set as the .RowSource propery of a Combo box. I've tried calling the function directly from the SQL statment

...
stringORDERBY = " ORDER BY CharacterReplacer(Nz([App Info].[Last Name]), ' ', '-', ''''), CharacterReplacer(Nz([App Info].[First Name]), ' ', '-', '''');"
Me.Names_Combo_Box.RowSource = stringSELECT & stringWHEREfirst & stringWHEREsecond & stringORDERBY

When I do it that way, I'm getting an empty Combo Box.

 

[Banaticus]

Where you putting that code in the query window (while looking at the query in SQL form)? I was putting it into the code for my main form. I tried to get this code to go with my query, but the Combo Box is still empty.

stringORDERBY = " ORDER BY CleanName([App Info].[Last Name]);" & vbCrLf &
   "Public Function CleanName(ByVal s As String) As String" & vbCrLf &
   "Dim v As Varient, i As Integer, temp As String" & vbCrLf &
   "v = Array(' ', '-', '''')" & vbCrLf &
   "temp = s" & vbCrLf &
   "For i = 0 To UBound(v)" & vbCrLf &
   "temp = Replace(temp, v(i), '')" & vbCrLf &
   "Next i" & vbCrLf &
   "CleanName = temp" & vbCrLf &
   "End Function"

But, the Combo Box remains empty.

 

[WayneRyan]

B,


stringORDERBY = " ORDER BY CleanName([App Info].[Last Name]);"

Then just put your function in a module and make it public.

On the Modules tab:

Public Function CleanName(ByVal s As String) As String
Dim v As Varient, i As Integer, temp As String
v = Array(' ', '-', '''')
temp = s
For i = 0 To UBound(v)
    temp = Replace(temp, v(i), '')
    Next i
CleanName = temp
End Function

Additionally, you can add a new column in the query grid and:

NewClm: CleanName([App Info].[Last Name])

Then you can undo the check (make it invisible), BUT still sort
on the field by choosing ascending/descending.

Wayne

 

[Banaticus]

What do you mean, "Add a new column to the query grid"? The SQL query is being created in VB -- it's not shown on the Queries list.

 

[WayneRyan]

Be

B,

IF you were designing queries in the QBE grid, you could reference your
Public Function in the query.

IF you are using VBA, you can use the function above, BUT you must
put it (not the definition) in the VBA string.

Wayne

 

[Banaticus]

Oh, I always type my queries in straight SQL. I've never really gotten the hang of that grid thing and it always seems somewhat limiting.

I put the public function in it's own module and it's all working fine now.