Questions tagged [cast]

Converts an expression of one data type to another.

Filter by
Sorted by
Tagged with
0
votes
0answers
16 views

How to case text array elements automatically?

According to PostgreSQL documentation on the ARRAY constructor: By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE ...
0
votes
2answers
34 views

Why does Casting this string as a decimal fail?

Why does casting this result of REGEXP_SUBSTR() to a DECIMAL fail? SELECT REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted, CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?&...
0
votes
1answer
53 views

Converting DATE and TIME integers to timestamp

I am trying to get a timestamp by combining date and time columns in my PostgresQL database and am running into errors. Here is what I have: +-----------+-----------+ | StartDate | StartTime | +-------...
0
votes
0answers
47 views

How to convert bit(n) to bytea when n >= 256?

How can I convert a BIT(256) into 64-byte BYTEA? More generally, how can I convert from BIT(n) to BYTEA when n is large enough that I can't cast the bits through an INT8? Example: I've got a bunch of ...
0
votes
1answer
18 views

Verify day falls in month

I have a table in BigQuery with Xday, Xmonth, and Xyear columns but the data was user generated and apparently done without adequate input validation so some of it is nonsense. It's easy enough to do ...
0
votes
0answers
79 views

Confused by boolean

After reading Casting between data types I wanted to create a table with a column that could store boolean values: create table t(a smallint default 0) insert into t(a) values (default) select cast(a ...
5
votes
2answers
2k views

PostgreSQL - CAST vs :: operator on LATERAL table function

While I can SELECT elem[1], elem[2] FROM ( VALUES ('1,2'::TEXT) ) AS q(arr), LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem ; using an explicit call to CAST, I can't SELECT elem[...
6
votes
4answers
2k views

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

I need to dig into a logs table with a schema similar to this: CREATE TABLE t ( id int PRIMARY KEY, data varchar(max) ); Column data stores a XML text received from a web service in this format: ...
1
vote
1answer
31 views

What is the best way to divide data for random groups?

I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was: SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) ...
2
votes
1answer
434 views

PostgreSQL ignores implicit casts in some situations

The setup (tried only on PostgreSQL 9.6): CREATE TYPE MY_ENUM AS ENUM ('a', 'b'); CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT; CREATE TABLE t (x MY_ENUM); INSERT INTO t VALUES ('...
1
vote
1answer
958 views

How to prevent implicit casting from bytea to text during the current connection in postgres?

In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting. I am working with some program code which automatically converts ...
-1
votes
1answer
2k views

MySQL Cast to TINYINT [closed]

I'm trying to cast a string data to a TINYINT data but I'm getting below error. SELECT CAST('100' as TINYINT); > 1064 - You have an error in your SQL syntax; check the manual that corresponds to ...
0
votes
1answer
25 views

Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22)

I know there are different variations of this question asked. But what I am trying to do is that, there is a table with a numeric column (12,0) that has data looking like Units ----- -1222222 0 -19 ...
2
votes
2answers
39 views

Where can I read about the behavior of the postgress casting operators?

I want to do something like this: alter table foos alter column bar type bool using bar::boolean; bar is currently a text column. Some experimentation shows that postgres will do things like convert ...
-4
votes
1answer
34 views

can I be helped? my querey should = something like .000534 however I get 0

My query result should be 0.000534, but I'm getting 0. Please help me. psql -Upostgres -dcans -c \ 'select (select COUNT(*) FROM brd.gaf_info where ...
0
votes
1answer
434 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
3
votes
2answers
2k views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
0
votes
1answer
119 views

Postgres - how to find contents of one field within another field

How do I find where the contents of one field are within another field? I have a database table with a auto-generated sequence number in one field, and another character based field which should ...
0
votes
1answer
68 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
2
votes
1answer
373 views

Is TEXT to VARCHAR(MAX) an implicit conversion?

I am copying TEXT columns to another table where the destination column is VARCHAR(MAX). It appears that this is an implicit conversion and that I do not need to write an explicit CAST for it. Is ...
0
votes
1answer
257 views

Casting fraction number to decimal throws Arithmetic overflow error

I have a table called AssignmentMarks that stores Student's assignment marks for different subjects. This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, ...
0
votes
1answer
189 views

PostgreSQL: What is the best way to cast from from text to point[]?

I'm looking for a way to cast from a text value: '(180,90),(-180,-90)' into a point[] value: {"(180,90)","(-180,-90)"} What is the best way to do this? One possibility that I can think of is to ...
1
vote
1answer
254 views

Convert Date and Filter by Year

I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field. For example, in my table there is a "Posting Date" field that is in the '20180401' ...
0
votes
2answers
204 views

SQL USING CAST in a query inside a stored Procedure

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error Conversion failed when converting date and/or time from character ...
0
votes
0answers
250 views

Writing CSV directly to a BYTEA column from PostgreSQL tables

Assuming these two tables: CREATE TABLE data (day DATE, kpi INTEGER); CREATE TABLE reports (csv BYTEA); Is it possible to (pseudocode): INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM ...
3
votes
2answers
294 views

UPDATE with CAST(meta->>“$.field”) failing due to “null”

This issue is affecting MySQL 5.7.25. So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At ...
-1
votes
1answer
3k views

Convert SQL Date MMDDYY varchar to Date?

In reading this, https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 http://www.sqlusa.com/bestpractices/datetimeconversion/ I cannot locate the ...
1
vote
2answers
2k views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
2
votes
2answers
7k views

Cast as int in CASE expression in SQL

I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the ...
0
votes
1answer
179 views

MS SQL -Date and Time Style conversion problem

I need to convert Date and Time Style from USA format to German and than cast that into int data type. So for variable in case below this works fine if I want to display it in Standard 112 (ISO) ...
12
votes
1answer
6k views

Why do I need to cast NULL to column type?

I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this: (Both the active and core fields are of type boolean) UPDATE fields as t set "active" =...
5
votes
2answers
19k views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
1
vote
0answers
89 views

Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked ...
1
vote
0answers
125 views

Is it possible to define a cast between text and a domain representing a uuid to avoid manual casts?

I created a DOMAIN as following: CREATE DOMAIN country_id AS uuid; I create such domains for readability. It is much easier to read function definitions which accept a country_id instead of a uuid. ...
6
votes
2answers
872 views

Is there a way to partially initialize a TYPE?

I have created a custom type: CREATE TYPE my_type as(name text, street text, location text); Initializing such a type is easy: SELECT ROW('a', 'b', 'c')::my_type; I am wondering if there is an ...
1
vote
0answers
685 views

Update BIT field with non-BIT value

I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native ...
2
votes
1answer
52 views

C++ stoi equivalent in SQL Server's T-SQL

I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts. Specifically, I'm looking to be able to grab numerics at the beginning of the ...
4
votes
1answer
188 views

How do I list available casts in SQL Server?

In PostgreSQL, there is a command, \dC+ I can use in psql \dC+, \dC[+] [PATTERN] list casts To lists the possible CASTS. This is useful, because I can easily find out what I can use to cast to ...
0
votes
0answers
19 views

Conversion Failed From nvarchar to int even when all values in result set are int [duplicate]

Take the following SQL sample: Declare @Table Table (Value int) Insert Into @Table Select intValueAsVarcharType From OtherTable Where Category = 'Type1' The OtherTable.intValueAsVarcharType ...
4
votes
3answers
4k views

Can MariaDB or MySQL cast as bigint, getting an error: … right syntax to use near 'bigint)'?

If I want to take a string and create a table (this fails in strict mode), CREATE TABLE g AS SELECT CAST('2147483699' AS int); How do I tell it that I want a bigint type? I'll also get an int(10) ...
1
vote
2answers
4k views

COALESCE with cast

I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get: ...
0
votes
1answer
39 views

In PostgreSQL, what is the behavior of the bit(n) -> “char” cast?

Using PostgreSQL if I cast 65::bit(8)::"char" I get 0, if I however cast 65::bit(8)::int::"char" I get A as I would expect. SELECT v, v::bit(8)::"char" AS noint, v::bit(8)::int::"char" AS "...
11
votes
5answers
13k views

PostgreSQL alternative to SQL Server’s `try_cast` function

Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use ...
0
votes
1answer
417 views

Average Value not working

I have a database of students and grades across varying report cycles. Each Report Cycle has a unique ID and each subject also has a unique ID. Students can have more than one teacher per subject so I ...
0
votes
1answer
386 views

how to set timestamp in datetime field if the time is not specified?

I have a Stored Procedure having input parameter as @startDate as datetime and I am using this parameter to compare this fields to table column. I want to convert that @startDate field if the ...
0
votes
1answer
359 views

Alter table with casting

Is there a way of run an alter table command with cast on MySQL databases? I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the ...
3
votes
3answers
5k views

float to varchar with no trailing zeros

I am having a heck of a time trying to convert a FLOAT(53) field to a VARCHAR where there are no trailing zeros or scientific notation. The field could have a number that is really big or really ...
2
votes
1answer
2k views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
0
votes
1answer
47 views

Special letter behaviour problem?

I asked question in very blurry manner because I cannot explain in short sentence what is my problem about. In general I have a set of procedures which transforms text and XML. Inside the procedures ...
19
votes
1answer
56k views

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server which hold a base64-encoded text string which I would like to decode into it's plain text equivalent Does SQL Server have any native functionality to ...