What replaces the single quote ' in SQL? 2

[herb8]

I need to use the single quotes in a string and Im wondering if there is anyway in SQL to be able to declare a variable as a varchar() and include the single quotes in the string?

(ie..
@sTmp varchar(50)

Set @sTmp = 'Hello World' ...but somehow keeping the singe quotes.
Thanks in advance,
Matt

 

[jmeckley]

Before I pass a string to the proc I search the string for a single quote ('). If I find one I append another single quote to it ('').

I hope this helps.

Jason Meckley
Database Analyst
WITF

 

[jmeckley]

I don't know what programming language you are using for the front end but here is a VBA Function I use to search for single quotes.

Public Function SingleQuote(ByVal Text As String) As String
On Error GoTo QuoteErr
    Dim I, X As Integer
    Dim TmpStr As String
    I = -1
    X = 0
    TmpStr = Text
    Do While I <> 0
        I = InStr(Text, &quot;'&quot;)
        If I > 0 Then
            TmpStr = Left(TmpStr, I + X) & &quot;'&quot; & Right(TmpStr, Len(TmpStr) - I - X)
            Text = Left(Text, I - 1) & Right(Text, Len(Text) - I)
            X = X + 2
        Else
            Exit Do
        End If
    Loop
    SingleQuote = TmpStr
    
Exit_Function:
    Exit Function
QuoteErr:
    MsgBox Err.Description
    Resume Exit_Function
End Function

I'm sure their is a cleaner way to insert strings into other strings, but this works. Hope this helps

Jason Meckley
Database Analyst
WITF

 

[ssmat]

Super tip. Thanks!

ssmat

 

[sunila7]

Hi,

not sure wht u r looking for....

declare @stemp varchar(50)

set @stemp = '''Hello world'''
print @Stemp

Does this help

Sunil

 

[bennynye]

For VB there is an easy "Replace" command as well:

sLookup = txtSearch.Text
sLookup = Replace(sLookup, "'", "''")

Just in case someone reads this and uses VB.

Have a good one.

Ben

 

[pascalsql]

Search in Books on Line, there is a built-in sql sp_proc that does exactly the job, but I don't remember the name and don't have the doc now.
Like sp_quotename or so...

Rule:
Enclose single quote with double quotes: @a = "5 o'clock"
Enclose double quote with single quotes: @a = 'Say: "hello"'
Or double the quote: @a = '5 o''clock' or : @a = "Say: ""hello"""

 

[jmeckley]

Bennynye

Thanx for the alternate method to search for a string! Much cleaner, here's a star!

Jason Meckley
Database Analyst
WITF

 

[SQLBill]

jmeckley,

When you are giving someone a star, you have to click on the "Mark this post as a helpful/expert post!" in THEIR post not yours. =)

-SQLBill

 

[jmeckley]

I thought I did? I clicked "Mark this post as helpful/expert post! under" bennynye post. Someone else gave me a star. Can you give yourself a star?

On my screen bennynye has a star next to his name now.

Jason Meckley
Database Analyst
WITF