Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: Replace in a SQL string

    Hello,
    As always, I have a question

    I want to replace in a SQL string.

    I want, for example, to replace de date in this SQL:

    Code:
    SQL="SELECT propietario, [P Total]
    FROM [qDatos Formulario] 
    WHERE ([qDatos Formulario].anho_desde*100+[qDatos Formulario].mes_desde>=200701 And [qDatos Formulario].anho_hasta*100+[qDatos Formulario].mes_hasta<=200712);"
    So I want to replace...

    Code:
    SQL=Replace(SQL, "<=??????", "<=" & Me.Controls(1).Value & "01")
    SQL=Replace(SQL, ">=??????", ">=" & Me.Controls(1).Value & "12")
    The thing is that I cannot find a wildcard for just one character. Like in DOS is the "?".
    The problem is that I don't always know the year&month number.

    Again, the question is: How to replace some text in a SQL?

    Thanks for your comments and ideas!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    the underscore _ is a single character wildcard.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2008
    Posts
    189
    Cool, thanks.

    Also is there any way to "split" the SQL and have a collection/vector or something with the selected fields.

    I want to do this replace more flexible.
    I want to replace for example the filter in another field.

    Like:
    Where (Field1="Bla" and Field1="Wow")
    And being able to "split" the "Bla" and "Wow" without having to do magic "Instr".

    Any ideas how?

    Thanks!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Erm.... huh?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2008
    Posts
    189
    I just want to replace the "Bla" or whatever the filter of the Field1 is by something else.

    Imagine that I don't know that in the SQL there is a "Bla" or a "Blaaa"... And I want to update the SQL with some new filters...
    I just want to know if there's some automatic way to change the filter of Field1.

    Ideas? Thanks!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Use a variable?

    EG:

    VariableName = "BLA"
    "Field1=""" & VariableName & """"


    Is that what you are after?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2008
    Posts
    189
    Mmm, no.

    I have a user that had created some (many) querys that I want to use again.
    And I don't want to read all the SQL code to find the parameters in the filters.

    So I was thinking that maybe there's some way to "split" the SQL into a vector/array or something more easy to manage.

    As I see.. I will have to read all the SQL code

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't get it. You let USERS dictate terms of database design?

    Have you examined parameters yet? Perhaps a parameter might be what you are after?

    SELECT * FROM sometable WHERE somefield = [Enter the stuff you want to see]

    Such a query will prompt the user for the criteria each time the query runs.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Apr 2008
    Posts
    189

    Question

    How could I solve this:

    Code:
    If InStr(vNuevoSQL, "WHERE*" & "SOME_TEXT" & "*GROUP BY") = 0 And InStr(vNuevoSQL, "SELECT*" & "SOME_TEXT" & "*FROM") > 0 Then
    I want to find "SOME_TEXT" inside an SQL

    The vNuevoSQL is this one:

    Code:
    SELECT TOP 5 [qDatos Formulario].[SOME_TEXT]
    FROM [qDatos Formulario]
    WHERE ([qDatos Formulario].[anho_desde]*100+[qDatos Formulario].[mes_desde] >= 200701 AND [qDatos Formulario].[anho_hasta]*100+[qDatos Formulario].[mes_hasta] <= 200712)
    GROUP BY [qDatos Formulario].[field1]
    ORDER BY [qDatos Formulario].[field2] DESC;
    The problem is that I have some "Line Feeds" in the SQL code, so I think the "*" (wildcard) is not working correctly.

    Any ideas how to find this text between the SELECT and FROM; and between WHERE and GROUP BY?

    Thanks!!!

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The SQL
    Code:
    "WHERE*" & "SOME_TEXT" & "*GROUP BY"
    Is incorrect.

    Let's assume "SOME_TEXT" has a value of "abc", that would give us the following strong
    Code:
    WHERE*abc*GROUP BY
    Now, because you're dealing with strings, the string within the SQL statement has to be surrounded by quotes itself! And also, let's not forget about those spaces!
    Code:
    WHERE '*abc*' GROUP BY
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2008
    Posts
    189

    Question

    I get your point, but I have the issue that I don't know what the text could be.

    I could have:

    Code:
    SELECT TOP 5 [qDatos Formulario].cisco
    SELECT TOP 551 [qDatos Formulario].phone
    SELECT [qDatos Formulario].cisco
    SELECT [qDatos Formulario].phone
    As you can notice, I could have TOP, 5, 55, none... So, I don't know the lenght of the text between SELECT and "cisco" or "phone".

    Any ideas?

    Thanks!

  12. #12
    Join Date
    Apr 2008
    Posts
    189
    Here is some code that solve the wildcard problem with the Instr:
    http://www.visualbasic.happycodings....er/code26.html

    Code:
    InStr with wildcards
    
    The following routine is the same as the instr routine, but it accepts the "*" wildcard:
    
    'Purpose     :  Same as Instr() but accepts the "*" wildcard.
    'Inputs      :  sSearchText         The text to search in
    '               sForText            The text to search for
    '               [lStart]            The position of the first character to search on
    '               [bCompareText]      If True, search is case insensitive
    'Outputs     :  Returns the position of the match or zero if no match was found
    
    
    Function Instr2(sSearchText As String, sForText As String, Optional ByVal lStart As Long = 1, Optional bCompareText As Boolean = False) As Long
        Dim lWildCardStartPos As Long, lWildCardPos As Long, lWildCardEndPos As Long
        Dim sSubtext As String, lCompare As Long
        
        'Set variables
        lWildCardStartPos = lStart
        If bCompareText Then
            lCompare = vbTextCompare
        Else
            lCompare = vbBinaryCompare
        End If
        
        Do
            'Search for wildcard
            lWildCardPos = InStr(lWildCardPos + 1, sForText, "*")
            If lWildCardPos Then
                lWildCardEndPos = lWildCardPos - 1
            Else
                lWildCardEndPos = Len(sForText)
            End If
            
            'Search input text
            sSubtext = Mid$(sForText, lWildCardStartPos, lWildCardEndPos - lWildCardStartPos + 1)
            lStart = InStr(lStart, sSearchText, sSubtext, lCompare)
            
            'Check exit conditions
            If lStart = 0 Then
                'Clear value and exit do
                Instr2 = 0
                Exit Do
            ElseIf Instr2 = 0 Then
                'Store value
                Instr2 = lStart
            End If
            If lWildCardPos = 0 Then
                Exit Do
            End If
            lWildCardStartPos = lWildCardPos + 1
        Loop
    End Function

  13. #13
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You could parse the whole sql statement using instr to find your search term and then using instr again to find the next space after your search term and repeat for each search term, but this seems like a lot of work. Perhaps there is a different approach. What is the end result that you want?

  14. #14
    Join Date
    Feb 2010
    Posts
    2
    I have is SQL in MS Access database and it works fine for letter "e".

    UPDATE Detail
    SET Artikel_Text = Replace(Artikel_Text,"e" & Chr(13) & Chr(10),"e")
    WHERE Artikel_Text Like "*" & Chr(13) & "*";

    Basically I'm stripping chr(13) and chr(10) from e.

    But I have more letters, a, b, c, etc till z and A, B, C, etc till Z. Numbers and other character should retain their chr(13) & chr(10).

    How can I automate this, I've tried wildcard with no luck.
    I don't want to make 52 of these to cover the whole alphabet.

    This would remove every chr(13) and chr(10) I don't want that.

    UPDATE Detail
    SET Artikel_Text = Replace(Artikel_Text, Chr(13) & Chr(10),"")
    WHERE Artikel_Text Like "*" & Chr(13) & "*";

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can read the queries collection
    having done that it shoudl eb possible using the REPALCE() function to repalce soemthign with somethign else.
    this assumes that the something is consistent.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •