[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Access SQL Expression to delete string after first white space (including asian character sets)

Hello Experts

I need help to write an expression that will delete any text string after the first occurence of a white space character.

This query seemed to do the job on some records, but not others.
SELECT T.Field, IIf(InStr(1,[T.Field," " Or " " >0,Trim(Left(T.Field,InStr(1,T.Field," " Or " "))),T.Field) AS Expr1
FROM T;

I copied and pasted the white space characters and put them into the query, but it doesn't recognize some of them.

I've attached an Access file with some test records.

Thank you!

Tom
white-space.accdb
0
tomfolinsbee
Asked:
tomfolinsbee
  • 6
  • 3
  • 2
  • +1
1 Solution
 
Vadim RappCommented:
Here's a sample:

https://filedb.experts-exchange.com/incoming/ee-stuff/8367-db.mdb

The query is using one-line custom function written in VBA.
0
 
datAdrenalineCommented:
First implement the following VBA into a Standard Module that does not share the name of either function ..

Public Function RemSpecial(strText As String, strReplaceWith As String, ParamArray strExceptions()) As String
'Removes special characters from a string.
'Usage:
'RemSpecial("My // text here") --> Yeilds: "Mytexthere"
'RemSpecial("My // text here", " ") --> Yeilds: "My text here"
'RemSpecial("My //\\ text here","\", " ") --> Yeilds: "My \\ text here"
    
    Dim x As Integer
    Dim y As Integer
    Dim strX As String
    Dim strStrippedText As String
    
    'Ascii codes
    Const conNumberRangeStart = 48
    Const conNumberRangeStop = 57
    Const conCapLettersStart = 65
    Const conCapLettersStop = 90
    Const conSmallLettersStart = 97
    Const conSmallLettersStop = 122
        
    'Loop through each digit of the string to determine if it falls into the
    'Ascii ranges OR is an exception
    For x = 1 To Len(strText)
    
        strX = Mid(strText, x, 1)
        
        Select Case Asc(strX)
        
            Case conNumberRangeStart To conNumberRangeStop, _
                 conCapLettersStart To conCapLettersStop, _
                 conSmallLettersStart To conSmallLettersStop
                 
                strStrippedText = strStrippedText & strX
        
            Case Else
                
                Dim blIsException As Boolean
                blIsException = False
                
                If UBound(strExceptions()) >= 0 Then
                    For y = 0 To UBound(strExceptions())
                        If strX = strExceptions(y) Then
                            blIsException = True
                            Exit For
                        End If
                    Next y
                End If
                
                If blIsException Then
                    strStrippedText = strStrippedText & strX
                Else
                    strStrippedText = strStrippedText & strReplaceWith
                End If
                
        End Select
    Next x
    
    RemSpecial = strStrippedText
    
End Function


Public Function GetAsciiLegend(strText As String) As String
    
    Dim x As Long
    Dim strTemp As String
    
    For x = 1 To Len(strText)
        strTemp = strTemp & Mid(strText, x, 1) & "(" & Asc(Mid(strText, x, 1)) & ") "
    Next x
    
    'Debug.Print strText & vbCrLf & vbCrLf & strTemp
    
    GetAsciiLegend = strTemp
    
End Function

Open in new window


Next, you can use  the following SQL statement to return columns of some information with the most important column being the "Scrubbed" column ...

SELECT test.Component
    , GetAsciiLegend([Component]) AS AsciiLegend
    , RemSpecial([Component],"|","(",")") AS CleanCharacters
    , Mid([CleanCharacters],1,InStr([CleanCharacters] & "|","|")-1) AS Scrubbed
FROM test;

Open in new window


The results of that query should be what you are looking for.
0
 
datAdrenalineCommented:
vadimrapp1,

Your file sample will not work for the original questioners issue.  You see the "white space" is not an ascii space.  If you download the file the questioner posted  (right click | save link as), then change the extension to .accdb, then open in Access, you will see what the original questioner is experiencing.

PS> I was hoping it would be a simple space as well! :) ..
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
datAdrenalineCommented:
PS> The comments in the header portion block of RemSpecial() that describe the usage are inaccurate.  I modified the code with the strReplaceWith argument and forgot to change the comments describing the usage.
0
 
tomfolinsbeeAuthor Commented:
Thank you!
How can i modify the code so that it only strips the text after the first white space? I still need to keep the Japanese characters that come before the first white space.
Cheers,
Tom
0
 
tomfolinsbeeAuthor Commented:
It looks like the troublesome white space is #32.

Could someone help with a query that would ignore anything to the right of the first #32 that is encountered in a string?

Thanks!
0
 
als315Commented:
Sometimes you have unicode IDEOGRAPHIC SPACE (&H3000), so you should search for this symbol also.
Look at sample (Query1)
white-space.accdb
0
 
tomfolinsbeeAuthor Commented:
Thanks. That caught all the white spaces.  I ran it on the full data set, but just noticed that there are a lot of records with a space in the 2nd position. Could you show me to how to modify the function so that it ignores any white space in the 2nd position (but continues to search the rest of the string)? Sorry about that, this case was in the test data.
0
 
tomfolinsbeeAuthor Commented:
correction: was not in the test data
0
 
tomfolinsbeeAuthor Commented:
I added two additional test records with the white space in the 2nd position.

Appreciate the help, sorry I missed these in the original test file.
white-space-extra.accdb
0
 
als315Commented:
We can add start position to function (second parameter)
white-space-extra.accdb
0
 
tomfolinsbeeAuthor Commented:
Thank you for this -- worked great. Sorry for the late reply.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now