Curly Braces in T-Sql

Curly braces in T-SQL

These are ODBC escape sequences. See Date, Time, and Timestamp Escape Sequences for more details.

There is also similar syntax for uniqueidentifiers

SELECT {guid '00000000-0000-0000-0000-000000000000'},

as well as procedure calls and some other constructs detailed off that link.

With regard to the rest of your question I'm not aware of any way of having an integer literal treated as a bigint or of any particular resource that lists all the ways of influencing how literals are assigned datatypes by SQL Server. Some ways are below.

;WITH cte(thing) AS
(
SELECT CAST(1 AS SQL_VARIANT) UNION ALL
SELECT $1 UNION ALL
SELECT 1e0 UNION ALL
SELECT 1.0000 UNION ALL
SELECT 2147483648 UNION ALL 
SELECT {ts '2011-09-15 01:23:56.123'}  UNION ALL
SELECT {d '2011-09-15'} UNION ALL
SELECT { t '13:33:41' }  UNION ALL
SELECT {guid '00000000-0000-0000-0000-000000000000'} UNION ALL
SELECT 'Foo' UNION ALL
SELECT N'Foo'
)
SELECT thing, 
       sql_variant_property(thing,'basetype') AS basetype,
       sql_variant_property(thing,'precision') AS precision, 
       sql_variant_property(thing,'scale') AS scale, 
       sql_variant_property(thing,'maxlength') AS maxlength
FROM cte

Returns

thing                          basetype            precision   scale  maxlength
------------------------------ ------------------- ----------- ------ ---------
1                              int                 10          0      4
1.00                           money               19          4      8
1                              float               53          0      8
1.0000                         numeric             5           4      5
2147483648                     numeric             10          0      5
2011-09-15 01:23:56.123        datetime            23          3      8
2011-09-15 00:00:00.000        datetime            23          3      8
2011-09-15 13:33:41.000        datetime            23          3      8
00000000-0000-0000-0000-000000 uniqueidentifier    0           0      16
Foo                            varchar             0           0      3
Foo                            nvarchar            0           0      6

How does this TSQL FROM statement work with curly braces and commas?

Curly braces are used to show ODBC escape sequences which are used within an SQL statement to tell the driver that the escaped part of the SQL string should be handled differently.

Here are some links with more info...

Curly braces in T-SQL

Understanding the use of curly braces and "OJ" in a SQL query

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-sql-escape-sequences?view=sql-server-ver15#:~:text=Escape%20sequences%20are%20used%20within,code%20that%20SQL%20Server%20understands.

How can I escape a caret, tilde and curly braces in T-SQL?

Don't escape it.

As you already found out, if you want to find the caret, i.e. you are interested in it as a character, you have to escape it.

WHERE SampleString LIKE '%[\^]%' ESCAPE '\'

When you are trying to find strings that do not contain something, i.e. use the caret for its "function", you have to not escape it:

where SampleString LIKE '%[^~{}]%' ESCAPE '\' 

You could actually use both escaping and not escaping in the following example, where you would get strings that contains something else other than the caret:

where SampleString LIKE '%[^\^]%' ESCAPE '\'

Find and Replace values in curly braces with values coming from other column

A recursive CTE is probably going to be rather unwieldy here, as you cannot use TOP and you must also filter only the final results.

Instead use a table variable or temp table, and update it in a loop.

DECLARE @results TABLE (id int, notes varchar(1000));

INSERT @results (id, notes)
SELECT id, notes
FROM @DataTable dt;

DECLARE @dtp varchar(100), @dtv varchar(100);

WHILE 1=1
BEGIN
    SELECT TOP (1)
        @dtp = dtp.parameterid,
        @dtv = dtp.value
    FROM @DataTableParameter dtp
    WHERE parameterid > @dtp OR @dtp IS NULL
    ORDER BY parameterid;

    IF @@ROWCOUNT = 0
        BREAK;
    
    UPDATE @results
    SET
        notes = REPLACE(notes, '{{param = "' + @dtp + '"}}', @dtv)
    FROM @results r
    WHERE notes LIKE '%' + @dtp + '%';
END;

SELECT *
FROM @results;

db<>fiddle

what does curly brackets {} do in a SQL query?

See http://www.php.net/manual/de/language.types.string.php#language.types.string.parsing for the double quote string syntax.

The curly braces are for complex variable expressions. They are interpreted by PHP, not by the SQL interface.

$query = "SELECT * FROM users WHERE user='$_POST['username']' AND password='$_POST['password']'";  

The above will lead to an parsing error. Without curly braces you have to write:

$query = "SELECT * FROM users WHERE user='$_POST[username]' AND password='$_POST[password]'";  

Note the lack of key quotes. This only works for a simple array access, and for a simple object property expression. For anything more complex, use the curly braces.

---

Now that you know that, do a pinky swear that you won't ever do so. Because interpolating user input directly there is not a good idea. http://bobby-tables.com/

Do yourself a favour and use PDO with prepared statements. So much easier.

---

But to give an example for a more complex curly string syntax, this is what I'd do:

$query = "SELECT * FROM users WHERE user={$_POST->id->sql['username']}";

(Does some inline filtering and quoting. Just as example, does not work with default PHP setups.)

Comma separated (with curly brackets ) search in SQL

If SomeID1 is unique you can do this:

select * from table2 
where (select replace(replace(Data1, '{', ','), '}', ',') from table1 where SomeID1=?) 
like concat('%,', Data2, ',%') 

This works for SQL Server and MySql and you can adjust it to work for any database.

See the demo.

How to Escape Curly Braces in MS-SQL

This works:

SELECT SUBSTRING(<col name>, CHARINDEX('CN={', <col name>) + 4, 32)

Double curly braces on SET operation sql

This is the syntax used in your specific program (Datazen Dataview) to substitute parameters.

So what you're seeing are that the SQL variables @prmFromDTand @prmToDT are being filled with parameters.

Reference:

In previous versions of Datazen, all parameter substitution was done
inline. This necessitated the use of quotes to wrap string values
(ex. WHERE x = '{{ @Xvalue }}'). While this is still the case for
non-DB connections types, it is advised not to do so for DB types.
While DB types will automatically unwrap the quotes from around a
parameter if the parameter is the only text contained within the
quotes, if any other text is present, the query will surely fail. For
example, while the above example evaluates to "WHERE x = @p1"
something like "WHERE y = 'P001-{{ @Yvalue }}'" will evaluate to
"WHERE y = 'P001-@p1'" which is clearly not proper SQL parameter
syntax.

Source:

https://social.technet.microsoft.com/wiki/contents/articles/32028.datazen-data-view-parameter-replacement-functions.aspx

Also reference on how to use parameters in datazen and the resulting syntax:
https://christopherfinlan.com/2015/06/10/how-to-enable-user-activity-reporting-on-your-datazen-dashboards/

---

Related Topics