Avatar of Richard
Richard
Flag for United States of America asked on

How can I find inbeded strings in SQL an field using the 'LIKE' operator using VB6

Hi Experts,
I'm trying to find records a substring within an SQL NVARCHAR field using VB6.  My select statement is:  
            sCont = "SELECT * FROM " & sTDSubProfile _
            & " WHERE Recording Like '%" & sOrg & "%'"
I'm trying to find all the records with sOrg in the 'Recording' field.  The field contains text before and after the sOrg value.  If I use the above select statement (replacing sOrg with the actual string) in the SQL Server Management Studio, I find numerous records.  However in VB6 no records are found.  I don't get any errors when using the above statement in the open command - just no records are found.  I suspect that '%' may not be the proper wildcard character to use.  Any ideas?
Microsoft DevelopmentMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Richard

8/22/2022 - Mon
Patrick Matthews

Try using * Instead of % as the wild card.
Richard

ASKER
Hi Matthew,
Same results.  I have tried all the wildcard characters I can think of (#,%,*) and none works. If I replace sOrg with the complete string for one of the records (still using "LIKE") it finds 1 record (as expected).
Brook Braswell

I would suggest to make sure you variable sOrg does not contain special chars it'self

do a trim on it, remove single quotes and *, and % also.

Your phrase appears to be fine, put a break in your code where the SQL statement is formed.

Show me what the value of sCont is before you do your query.
see this link for more information on SQL Server 2008 r2

http://msdn.microsoft.com/en-us/library/ms189454.aspx

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Richard

ASKER
Hi Brook,
here is the result of the Debug.Print statement after the creation of the sCont:
SELECT * FROM SubProfiles WHERE Recording Like  '%\\Recordings%'
sOrg = \\Recordings
sOrg comes from me typing the value into a text box
your link shows 'Dan%' without the leading "%" to find inbeded strings however if (in SQL) i use my select statement it works.  I have over 10K records with "\\Recordings" - which I need to change to "\Recordings"

ASKER CERTIFIED SOLUTION
Brook Braswell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Richard

ASKER
Hi Brook,
I'm stupid sometimes - I should be searching for "Recordings\\" not "\\Recordings".  after fixing that everything works as expected.  One thing - your suggestion about using the UPDATE won't change just a part of a string field - "Recordings\\" is inbeded within a longer string.  While your response didn't actually solve my problem, it did cause me to look closely at my issue - which sometimes is just as good.  Thanks for the speedy responses!
R.