delimiter problem in SQL SELECT statement

[applek]

Hi, All.

I'm using SQL server and would like to know that if a record contain a string with few delimiter, For instance:

FieldA FieldB
1 Sister's Friend
2 Cousin's Brother's food

If i would like to retrieve the particular FieldA by passing the FieldB value:

SELECT FieldA from TblABC WHERE FieldB = 'Sister's Friend' - I'm sure this already given an error.

Is there anything i can add so that to retrieve the FieldA value wihout changing the value of FieldB? I understand that some programmer they will add some alphabet to recognize the "'" (say "2" to replace "'&quot during the insertion/updating, but my point is I wouldn't like to see the FieldB value to be "Sister2s Friend" in the database instead of remaining the original state?

Pls help. Thank you for your attention.

 

[Transcend]

For cases where you have a ' in your string you need to do this

SELECT FieldA from TblABC WHERE FieldB = 'Sister''s Friend'



Transcend

 

[SemperFiDownUnda]

Don't change the character. You just have to remember to change all single quotes in your strings to 2 single quotes before you add or update data.

SQL server will interpret the 2 single quotes as 1 single quote instead of a string delimiter.

This is common with special characters that have other meanings in a language like
&& means & because & by itself oftem means a hex number is coming up or
%% means % because % often is used with a char to allow substitution.

 

[applek]

ok...you are right.
I try to clarify the situation:

In VB, I have this variable strValue.
This strValue is captured in the application. Ex: strValue = Sister's Friend.

In the VB program, I have to retrieve the ID for the corresponding strValue. So, i have to pass the strValue which contain "'". Now, i manage to select a statement with single quotation mark in SQL query analyser:
SELECT * FROM tblABC where FieldB = "Sister's Friend". It is no problem to do so. This only thing is the VB program:

strSQL = "SELECT * FROM tblABC WHERE FieldB = '" & strValue & "'" - this is the normal we practise.

Anyway, i do solve the problem. Thanks anyway.

 

[jincy]

applek,
once i faced the same problem. this code will help you..

strValue .Text = Replace(strValue .Text, "'", "''&quot
strSQL = "SELECT * FROM tblABC WHERE FieldB = '" & strValue & "'"