Link to home
Start Free TrialLog in
Avatar of Richard
RichardFlag 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?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Try using * Instead of % as the wild card.
Avatar of 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).
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

Avatar of 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
Avatar of Brook Braswell
Brook Braswell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.