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
tomfolinsbeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.