Skip to main content
Share Your Experience: Take the 2024 Developer Survey
48 votes
Accepted

Decode Base64 String Natively in SQL Server

Figured it out: SELECT CONVERT ( VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)') ) AS RESULT FROM ( ...
GWR's user avatar
  • 2,807
35 votes

PostgreSQL alternative to SQL Server’s `try_cast` function

Rationale It's hard to wrap something like SQL Server's TRY_CAST into a generic PostgreSQL function. Input and output can be any data type, but SQL is strictly typed and Postgres functions demand ...
Erwin Brandstetter's user avatar
19 votes
Accepted

Why do I need to cast NULL to column type?

This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here: SELECT pg_typeof(NULL); pg_typeof ─────────── unknown This changes when a VALUES table comes into ...
András Váczi's user avatar
18 votes
Accepted

Why is to_char left padding with spaces?

You can see that in a simpler test case here SELECT '>' || to_char(1, '0') || '<'; ?column? ---------- > 1< (1 row) This is because, as @Abelisto said, the space is reserved for the ...
Evan Carroll's user avatar
  • 63.5k
14 votes
Accepted

PostgreSQL custom operator UUID to varchar

What you to do is CREATE CAST not an operator. This is the problem: SELECT pg_typeof(uuid), uuid = uuid::varchar AS eq FROM gen_random_uuid() AS t(uuid); ERROR: operator does not exist: uuid = ...
Evan Carroll's user avatar
  • 63.5k
12 votes
Accepted

How exactly does the one-byte "char" type work in PostgreSQL?

1. chr(10) ... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there. 2. & 3. ascii() produces 128 ...
Erwin Brandstetter's user avatar
11 votes
Accepted

Inserting date from string: CAST vs CONVERT

The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-...
Solomon Rutzky's user avatar
9 votes
Accepted

Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

SELECT thing, sql_variant_property(thing,'basetype') AS basetype, sql_variant_property(thing,'precision') AS precision, sql_variant_property(thing,'scale') AS scale FROM (VALUES ...
Martin Smith's user avatar
  • 84.8k
9 votes
Accepted

Average Value not working

You can't get more precision from an INT value than a whole number. You need to make them decimals before calculating: CREATE TABLE [grades] ([intGradeTransposeValue] INT, [...
LowlyDBA - John M's user avatar
9 votes
Accepted

Casting an array of texts to an array of UUIDs

Like has been commented, the column project_ids should be uuid[], which would preclude the problem. It would also be more efficient. To change (with no illegal data in column like you asserted): ALTER ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Postgres strange conversion of the string 'now' to timestamp

'now' (with any amount of leading and trailing spaces and brackets ({[( )]}, which is trimmed as noise) is a special date/time input constant. It evaluates to the timestamp of the current transaction. ...
Erwin Brandstetter's user avatar
7 votes
Accepted

Is TEXT to VARCHAR(MAX) an implicit conversion?

As detailed in the excellent Microsoft Documentation, conversions from text to varchar can indeed be implicit.
Hannah Vernon's user avatar
  • 70.1k
7 votes

Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

Your XML stored in a varchar(max) column should look like this. <?xml version="1.0" encoding="UTF-8"?> <PARAM> <TAB DIM="30" ID="ZC3D2_1" SIZE=&...
Mikael Eriksson's user avatar
7 votes
Accepted

Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

What's happening here is: The XML type stores data internally as UTF-16 Little Endian (most of the time, at least). It doesn't matter what the source encoding is, the end-result will be UTF-16 LE (...
Solomon Rutzky's user avatar
6 votes
Accepted

casting empty string as null datetime

Besides Gary's answer, I came up with the following: cast(NULLIF([expected_discharge_dttm],'') as datetime) Can anyone foresee an issue with this code (or Gary's use of CASE)?
pmdci's user avatar
  • 691
6 votes
Accepted

How do I cast varchar to macaddr without changing client code

Shamelessly stolen from Craig's answer over SO. You need to create a cast to make the varchar -> macaddr coercion work automatically. It is slightly tricky, as there is no function that does exactly ...
András Váczi's user avatar
6 votes

changing float->numeric casts from assignment to implicit, dangerous?

Please be aware that different rounding algorithms are used for different data types. https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE When rounding ...
Alex's user avatar
  • 429
6 votes
Accepted

PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()?

The difference between the string->tsvector cast and the function-constructor to_tsvector(text) the cast (::) assumes the input is already a tsvector that's been casted to text. It does not use ...
Evan Carroll's user avatar
  • 63.5k
6 votes
Accepted

Special letter behaviour problem?

The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string ...
Aaron Bertrand's user avatar
6 votes
Accepted

float to varchar with no trailing zeros

SQL Server 2012 and up support FORMAT, which enables .NET-like format strings, making this a rather easy task; just use FORMAT(Num, '0.#####') Working SQL Fiddle here.
Glorfindel's user avatar
  • 2,201
6 votes
Accepted

How do I list available casts in SQL Server?

The Microsoft Docs page for CAST and CONVERT provides a great matrix of conversions in SQL Server. The image included in the above page: As far as I know, there is no built-in mechanism for ...
Hannah Vernon's user avatar
  • 70.1k
6 votes

Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

I think you have a deeper problem. UTF-8 allow for more characters than the regular non-Unicode collations in SQL server. So, to be safe you should either use SQL Server 2019 which has UTF-8 ...
Tibor Karaszi's user avatar
6 votes

PostgreSQL - CAST vs :: operator on LATERAL table function

It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression. So that is indeed a trick you can use if you want an ...
Laurenz Albe's user avatar
  • 52.8k
5 votes
Accepted

Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered

Use try_cast() instead. select * from ( select * from myNormalTable AA inner join #TEMP BB on try_cast(BB.NVARCHARCOL as bigint) = AA.MYBIGINTCOL --where try_cast(BB.NVARCHARCOL as bigint) is ...
SqlZim's user avatar
  • 2,420
5 votes
Accepted

Cast varchar into int, converting any value under 0 to 0

As TINYINT only allows 0-255 you could also use ISNULL(TRY_CAST(YourCol AS TINYINT),0) The TRY_CAST above returns NULL if supplied an invalid input for the datatype (including -ve numbers) and I ...
Martin Smith's user avatar
  • 84.8k
5 votes
Accepted

what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server

-- casting using DECIMAL(38,2) throws exception, Size needs to be casted as bigint select [SizeInMB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files -- This fix is ...
corning's user avatar
  • 207
5 votes
Accepted

Is there a way to partially initialize a TYPE?

Code examples based on your type definition: CREATE TYPE my_type AS (name text, street text, location text); Your syntax is not possible (as you already know). A plain cast does not allow a (partial)...
Erwin Brandstetter's user avatar
5 votes

Cast as int in CASE expression in SQL

Why are you casting the substring as an int in the first place? The consumer is going to expect that column to output as a string in every other case. So my suggestion: Just remove the cast. If you ...
Aaron Bertrand's user avatar
5 votes
Accepted

Convert Date and Filter by Year

It would be much better if you used a proper type for dates, i.e. DATE and not VARCHAR, as that would give you several advantages: integrity: prevents nonsense data to be inserted in the column, like ...
ypercubeᵀᴹ's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible