Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: Some in-line parsing in a query

    Ok, so I have a database column called Language. I need to do some summarizing on it. Specifically, I need to do something like:

    select language, count(language) from
    table
    group by language

    The only problem is that for a given language, the value can vary a little. For instance the language "arabic" could be in the database in any of the following variations:

    Arabic (5)
    Arabic
    Arabic - 1
    Arabic, 7

    Etc. I have come up with a rule as follows:

    Find the non-letter character and just take everything before that. Now, I need to know how to do this inline in a select. Is that possible? Thanks a lot all!

  2. #2
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    15
    First create a function in a module which returns the valid letter characters. See Example below..

    Function RemoveUnwantedCharacters(Str As String) As String
    'This function will find the first non-letter character and return
    'everything before it.

    Dim Position As Integer
    Dim Character As String
    Dim AscVal As Integer

    For Position = 1 To Len(Str)
    'Cycle through each character in the string
    'and return the ASCII code for it.
    Character = Mid(Str, Position, 1)
    AscVal = Asc(Character)
    'Compare ASCII values and exit loop if not valid
    'Capitals A-Z = 65 - 90
    'lower case a-z = 97 - 122
    If AscVal < 65 Then Exit For
    If AscVal > 90 And AscVal < 97 Then Exit For
    If AscVal > 122 Then Exit For
    Next Position

    RemoveUnwantedCharacters = Left(Str, Position - 1)

    End Function


    Then include this function in your query as follows..

    SELECT Count(RemoveUnwantedCharacters([Language])) AS Expr1
    FROM [Table];

Posting Permissions

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