Tag: T-SQL

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw someone trying to get the database context recently and they were confused. They had some code referencing @dbname, but received an error with this code. I thought maybe they meant @@DBMAME, but when I looked, there isn’t a variable tracked by SQL Server.

A quick search (really experimenting with SQL Prompt) showed me there is a DB_NAME() function. Without a parameter, this returns my current context. That’s useful in programming systems.

If I pass in a database ID, I get the name.

I haven’t typically had an issue here. In code, if I doubt anything, I issue a USE statement to set context, but if you are looking to take actions in a generic script based on the database, this can help.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A quick one today, just looking for strings. I wrote an article on this, so there’s more detail there, but here’s a bit of code you can look through and see what it does.

CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );

GO

INSERT dbo.MyTable

    ( mystring )

  VALUES

    ( ‘This is a 7’ )

  , ( ‘There is a 7 in this string’ )

  , ( ‘Why must 7 always be here’ )

  , ( ‘No 7s here. JK’ )

  , ( ‘My 7s here’ )

  , ( ‘An 7s here.’ )

  , ( ‘On 7s here.’ );

GO

SELECT

    ‘SetPosition’ = SUBSTRING(mystring, 4, 1)

  , ‘FindThe7’ = SUBSTRING(mystring, CHARINDEX(‘7’, mystring), 1)

  FROM

    dbo.MyTable AS mt;

DROP TABLE dbo.MyTable;

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In working through the Advent of Code and solving some of the problems in SQL, I found that I needed to take hex values and convert them to strings. In other words, I had a value like this:

select @hex = 0x3c044139f4fe36d7df0f4e87f948fc52

and I needed to determine if the first few characters (5 or 6), were 0s. In other words, I wanted to look at this part of the data above as a string.

3c044

I thought this would be simple. I tried this

select @value = CAST( @hex as varchar(50))

That’s my default, as it reads nicely. However the returned this:

<A9ôþ6×ßN‡ùHüR

That’s strange. I then tried CONVERT:

select @value = convert( varchar(50), @hex)

I got the same result. Why am I not getting the same value as a string? I looked at a few other code samples from others, and they looked the same, so I checked the documentation for CONVERT. I saw this:

Binary Styles: When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

Under the table, the information for 1 or 2 as a style has this:

If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

…

The characters 0x will be added to the left of the converted result for style 1.

All of that essentially means that if I use the default, 0, or have nothing, I get the binary data converted to to the binary bytes in ASCII. If I use 1 or 2, I get the string. Here’s a shot of the difference:

Two lessons. First, learn the data types and how they convert. Second, read the documentation carefully when things don’t work as expected.