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

Questions tagged [varchar]

Normally refers to a variable length string data type.

Filter by
Sorted by
Tagged with
0 votes
2 answers
199 views

Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so

"Often, you get the right outcome without doing so." Example: select CONVERT(varchar, getdate(), 112) outputs 20240417 I saw this in quite a few places on Stack Exchange until I found a ...
questionto42's user avatar
0 votes
1 answer
41 views

Will NVARCHAR(max) columns benefit from row compression?

I have a table that I believe owes most of its size to a huge NVARCHAR(max) column. I do not know how to test where most of its size comes from. Regardless, would such a table benefit from row ...
J. Mini's user avatar
  • 433
0 votes
0 answers
36 views

Reduce varchar column size in a production database

We have a database running on AWS Aurora Mysql with version 8.0.mysql_aurora.3.04.1 as a regional database. There is a table in it with a column with varchar size 200. We want to reduce it to varchar(...
Tanmay Sule's user avatar
0 votes
3 answers
150 views

How can a column's data type be safely reduced from NVARCHAR(max)?

My biggest table owes most of its size to a stupid NVARCHAR(max) column that I wager could easily be NVARCHAR(4000) or smaller. What steps can I take to become confident that this change is safe to ...
J. Mini's user avatar
  • 433
0 votes
1 answer
28 views

Why does Redshift ignore length when grouping by VARCHAR of only spaces?

Imagine I have a column name that is n rows and the nth entry is equal to REPEAT(' ', n). For SELECT DISTINCT name FROM table I would expect to receive n rows because each name is unique by definition....
Tanner Clary's user avatar
2 votes
1 answer
77 views

Column accepting more characters than defined

I have a column defined as character varying(20). But it is accepting more than 20 characters. I've already searched but couldn't find anything about it (maybe I'm searching wrong). Has anyone ...
FranS's user avatar
  • 23
2 votes
1 answer
349 views

Why does is JSON_EXTRACT's return value treated differently depending on the operator?

I'm using MySQL 8.0 (inside Azure Database for MySQL Server). I've noticed some strange behavior while querying with the "json_extract" functionality. In the example below, I am querying a ...
WannabeCoder's user avatar
2 votes
1 answer
256 views

CHAR and VARCHAR lengths for multibyte characters

I have a table with ~100mln rows. One of the columns is a VARCHAR(64) with utf8mb4 encoding for storing user nicknames (others are few integers). The data in that column has MAX(LENGTH()) == 42 and ...
herhor67's user avatar
2 votes
3 answers
134 views

Find "naked" varchars

It is clear that omitting the length from a varchar is a bad thing. Unfortunately I am now working with a code base where this has happened. Extensively. I would like to correct this. The first step ...
Michael Green's user avatar
0 votes
2 answers
120 views

Varchar for columns that store numerics/ids?

There is a table that has a column defined as follows: text_id varchar(255) NOT NULL I know that using varchar is optimal for storing text because it allows for variable length columns but in this ...
Jim's user avatar
  • 123
2 votes
2 answers
1k views

XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels

This is the error message I get when running the query below: Msg 6335, Level 16, State 102, Line 43 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. ...
Marcello Miorelli's user avatar
1 vote
1 answer
1k views

When does Oracle still treat empty strings as NULL?

I was looking for the difference between VARCHAR and VARCHAR2 and learned, among other things, that Oracle’s VARCHAR2 treats empty strings as NULL, or the other way round. Also, apparently I should be ...
Manngo's user avatar
  • 2,839
2 votes
2 answers
12k views

SQL Server error JSON text is not properly formatted. Unexpected character '?' is found at position 21

I am getting below error while parsing any JSON string with special character or emoji. How to parse this? JSON text is not properly formatted. Unexpected character '?' is found at position 21. ...
Mish's user avatar
  • 21
0 votes
1 answer
550 views

Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)

On my project (MariaDB 10.3), I use a table for storing data per ISIN code (a financial asset identifier). For instance, "US3696041033" for General Electric. I have 4 Millions rows in my ...
moe69230's user avatar
0 votes
1 answer
111 views

Working with varchar field in Clustered Index. (Performance Test)

I am using Microsoft SQL Server 2016. For my products table; I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)). I know ...
omerix's user avatar
  • 91
0 votes
1 answer
459 views

REPLACE() function accepts and returns non-character data types

The Microsoft documentation indicates that the REPLACE() function accepts and returns only character data types. However, these work without error: SELECT REPLACE(123456, 'X', 'Y') ; SELECT ...
UnLogicGuys's user avatar
3 votes
1 answer
414 views

Why does the MySQL CHAR datatype have no preceding length byte?

In MySQL documentation one can find that CHAR datatype has no preceding byte with string length. In contrast VARCHAR datatype has 1 or 2 preceding bytes where string length is stored. Which is ...
Marcin Badtke's user avatar
0 votes
1 answer
602 views

Compare occurence of chars in PostgreSQL string

I have some data like this: metaphone lag FLKSW [null] PPS FLKSW PPS PPS PSP PPS And I want to compare the string values in both columns on the folowing condition: they're similar (assign some ...
willsbit's user avatar
1 vote
1 answer
269 views

How to retain same FLOAT value after converting it to a VARCHAR in Azure Synapse

Consider the following example: DROP TABLE dbo.TEST_FLOAT; CREATE TABLE dbo.TEST_FLOAT ( AMOUNT FLOAT ); INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25); SELECT AMOUNT, CAST(AMOUNT AS ...
Lauren_G's user avatar
0 votes
0 answers
237 views

Change Column size of a table

We have a mysql 5.6 cluster, 1 master 5 slaves. Requirements:- We have to modify the size of a column varchar from 20 to 35 for 2 databases. What is the best approach for doing this accross the ...
Rajan Sharma's user avatar
2 votes
1 answer
2k views

Sensible max_length for Postgres db charfields

I am using Django with a Postgres backend. In the Django models, I have to set the max_length attribute on the Charfields, then the Django ORM deals with the database in the backend. In a lot of cases,...
logicOnAbstractions's user avatar
5 votes
1 answer
2k views

Why does COALESCE prevent the use of an index on a varchar, but not on a text column?

Imagine a view which contains a COALESCE of two varchar columns from two different tables. The underlying varchars are indexed in both tables. In Postgres 11.6, filtering this view on the result of ...
LondonRob's user avatar
  • 233
2 votes
1 answer
2k views

Update an nvarchar column with a new json value, without adding quotes to the new value

I've got the following table, where [Data] is of type nvarchar(max) and its values are valid json strings: Id Data 1 { "info" : { "done": true, "chosenOptions": ["...
HeyJude's user avatar
  • 447
2 votes
0 answers
1k views

Is it worth using a JSON column type, even if it's only used for simple serialized strings or integers?

I have a table with an unidexed value column of VARCHAR(1024), and I've used my ORM to automatically convert it to and from JSON. The entire reason for this is I want it to be able to store any data ...
Peter's user avatar
  • 121
5 votes
2 answers
2k views

How to change an incorrect datatype for a birth date field from VARCHAR to DATE

I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR! So now my users table looks like this: CREATE TABLE IF NOT EXISTS users ...
Lion Smith's user avatar
0 votes
2 answers
1k views

Error on updating a field with binary values

I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the ...
JRA's user avatar
  • 149
1 vote
3 answers
3k views

Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS

I am loading a table with the Japanese names from CSV file that are changing into question marks into SQL Table. The table that is storing these values is a varchar column. I understand that the ...
Nitesh Parasher's user avatar
2 votes
2 answers
7k views

varchar(n) size?

When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
d4bbi's user avatar
  • 45
0 votes
1 answer
602 views

Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues?

Everyone, the table relationship diagram for the database is the below one In case the full DDL for the database is requested, I can provide it (although it will probably exceed the character limit ...
query_question's user avatar
0 votes
1 answer
420 views

How to store long string with netstring format in varchar column?

I am involved in project where we have to store very long string in varchar column (varchar(max)). Due to this case have to questions: This column is one of other 40 columns in table (but only this ...
axdna's user avatar
  • 111
1 vote
4 answers
5k views

Why does SQL Server say it can't convert varchar to numeric?

I have the following schema/data in MSSQL Server 2019 (SQLFiddle): CREATE TABLE products( idn NUMERIC(9) PRIMARY KEY ); CREATE TABLE sales( idn NUMERIC(9) PRIMARY KEY, pid VARCHAR(50) ...
Nishant's user avatar
  • 879
6 votes
2 answers
1k views

Store VarChar(Max) [Notes] Column on Same or Separate Table

Disclaimer I had and idea for a table design a little while ago that made sense to me at the time. In recent history I have realized I have a habit of "over engineering" or spending too much time ...
Kirk Saunders's user avatar
1 vote
1 answer
1k views

Composite index performance with varchar columns

I have large fact tables with composite non-clustered indexes, something like this: NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey) The columns ending with "Key" are varchar(...
Avi's user avatar
  • 265
1 vote
1 answer
948 views

Changing all character varying to text across all tables and columns

I'm trying to find a way to iterate over all of my tables and changing each charvar[n] to text and charvar[][n] to text[]. However, only found ways to change it for a specific table and column or ...
JLev's user avatar
  • 135
2 votes
2 answers
443 views

charindex thrown off by extended characters

I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) ) This works ...
jfrobishow's user avatar
2 votes
1 answer
4k views

How to implement a unique key for a long VARCHAR column in MySQL

I need to let MySQL to check the uniqueness of a VARCHAR column which is 5000 max length and is not the primary key. I can use an index of length 500 on the column to speed up query, but how to create ...
jw_'s user avatar
  • 135
4 votes
2 answers
799 views

Different characters, same ASCII code?

I have this query that throws two results: SELECT id FROM table1 WHERE id like 'nm041033%' nm0410331 nm0410331 And this slightly different query that throws only one result: SELECT id FROM table1 ...
Leopoldo Sanczyk's user avatar
0 votes
1 answer
240 views

What is the advantage of setting a character column as VARCHAR(32) over VARCHAR(255)

What is the advantage of setting a database column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?
Navaneet Raman Bhaskar's user avatar
1 vote
2 answers
133 views

SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database?

Suppose a global, mid-sized company is considering a document control management solution that is backed by a SQL Server database and allows developers to create document form types using a form ...
plr108's user avatar
  • 125
0 votes
1 answer
680 views

VARCHAR automatically changes to VARBINARY

I am new to MySQL. When I declare a column as VARCHAR, why does it automatically change to VARBINARY? How should I avoid this? Database changed ...
unknown29's user avatar
17 votes
5 answers
21k views

How to add 1 milliseconds to a datetime string?

Based on a select, I can return x rows like this: 1 2019-07-23 10:14:04.000 1 2019-07-23 10:14:11.000 2 2019-07-23 10:45:32.000 1 2019-07-23 10:45:33.000 We have all milliseconds with 0. Is ...
Racer SQL's user avatar
  • 7,386
0 votes
2 answers
784 views

Varchar storage and performance

I have a simple query, It might be answered before but I didn't find the precise answer for the same. My question is: I created a table using JPA through entity. Now I shows string fields as ...
Pawan Tiwari's user avatar
6 votes
1 answer
146 views

Issue with variable length string between compatibility level 80 and 100

I was getting tons of issues related with a specific scenario on my current enterprise. We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to ...
Pablo Guzman Labra's user avatar
18 votes
2 answers
5k views

Using column size much larger than necessary

I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. ...
elbillaf's user avatar
  • 291
3 votes
2 answers
5k views

Storing unicode text into varchar column in SQL Server

I have mistakenly stored unicode text into a varchar column in a table and now it is shown as question marks (???). Is it possible to get our main information back from the column instead of question ...
Asrar Ahmad Ehsan's user avatar
15 votes
1 answer
8k views

If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

I'm asking this out of curiosity, being inspired by this question. We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a ...
mustaccio's user avatar
  • 26.1k
0 votes
2 answers
407 views

Fix OGR2OGR "failed when converting the varchar value to data type int" issue in the MSSQL table -

I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables. I have tried to get a reply in the GIS SE ...
GeorgeC's user avatar
  • 115
0 votes
1 answer
637 views

External Table: VARCHAR in Create but CHAR in Access Parameters

Consider this SO Question UTF-8 And External Tables. A proposed solution contains VARCHAR2 columns in the CREATE section but those same columns get CHAR attributes in the ACCESS PARAMETERS section. ...
zundarz's user avatar
  • 103
1 vote
2 answers
2k views

Converting Varchar to Int error

I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen. I have a column that is Varchar(20). It is used to store just year data (2010, 2011, etc.) ...
DBA Greg14's user avatar
0 votes
2 answers
2k views

2018 of MySQL (InnoDB) CHAR vs VARCHAR

In 2018, does it still hold true that MySQL (InnoDB) CHAR performs better VARCHAR speed-wise? I've only found a performance comparison explanation that is 7 year old. I've checked the official MySQL ...
Amio.io's user avatar
  • 103