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?
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?
Last Comment
Patrick Matthews
Try using * Instead of % as the wild card.
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).
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
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
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"
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
