sql-servercommand-line-interfacetilde

What does ' ~' mean in sql cli prompt and how do I escape from it?


I was playing around in the SQL command line and I accidentally entered a command incorrectly. Upon doing that the prompt changed from the expected '>' to '~' and I cant enter any new commands or make the tilde character go away

What does this mean and how do I escape from it?

My attempts below:

   select serverproperty'''sdf('''')
4~ go
5~ ;
6~ go
7~ undo
8~ stop
9~ quit
10~ quit
11~
12~
13~
14~
15~ quit
16~ stop
17~ go
18~ :EXIT\
19~ :EXIT
20~ exit
21~ EXIT
22~ EXIT
23~ GO
24~ Q
25~ exit()
26~ :Exit()
27~ :EXIT()
28~ GO
29~ select serverproperty('edition')
30~ go
31~ select serverproperty('edition')
32~ go
33~ ;
34~

Solution

  • You're inside a single quote string. You started your string off with ''' which opens the string, and then has an escaped single quote in it. Then you kept expanding said string. Then you never had a single quote (') again so you never excited the literal string.

    This animated gif might help explain further as well (this is sqlcmd but mssql-cli operates the same way):

    enter image description here

    First you have the statement below:

    1> SELECT 1 AS one,
    2>        2 AS two;
    3> --Single command, with 2 lines, no quoted string
    4> GO
    

    This doesn't have a tilde (~) character as you aren't inside a string literal.

    Then you have the second statement:

    1> SELECT 'Line 1
    2~ Line 2
    3~ Line 3' AS String;
    4> --Single command, 3 lines, 1 string
    5> GO
    

    This has tildes on lines 2 and 3 as they are within a literal string.