Questions tagged [varchar]

Normally refers to a variable length string data type.

Filter by
Sorted by
Tagged with
2 votes
1 answer
83 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
1 vote
1 answer
50 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
123 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
46 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
526 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
247 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,709
2 votes
2 answers
7k 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
366 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
87 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
309 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
387 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
425 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
195 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
190 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
1k 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
  • 223
1 vote
1 answer
1k 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
  • 437
2 votes
0 answers
791 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
983 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
  • 129
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
1 vote
2 answers
5k 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
  • 35
0 votes
1 answer
492 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
288 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
  • 101
0 votes
4 answers
4k 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
  • 827
5 votes
2 answers
949 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
973 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
643 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
344 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
3k 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
713 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
220 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
127 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
558 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
15 votes
5 answers
18k 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,344
0 votes
2 answers
605 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
137 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
17 votes
2 answers
4k 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
  • 281
3 votes
2 answers
4k 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
7k 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
  • 24.8k
0 votes
2 answers
393 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
596 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
3 votes
2 answers
5k views

Remove invisible null characters a string's ending

For an unknown reason, many strings in one of my VARCHAR(1000) columns have been terminated with invisible characters. declare @BrokenString varbinary(max)=...
A_V's user avatar
  • 1,334
5 votes
1 answer
14k views

Performance difference between Text and Varchar in Mysql

I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table ( pk int(11) NOT NULL AUTO_INCREMENT, varc varchar(255) DEFAULT NULL, txt ...
Dinesh Kumar's user avatar
1 vote
1 answer
285 views

Should I created a different table to hold a big text / varchar field in MySQL 5.5.61 with Antelope file format?

I read that newer version of MySQL save the big text fields apart from the rest of the row, so we don't have to bother about it. However, how about an older version, specifically MySQL 5.5? Is it ...
Calvin's user avatar
  • 23
1 vote
0 answers
730 views

Would using varchar(31) be bad compared to varchar(32)?

I wonder whether using varchar(word*n) would be more efficient compared to using varchar(word*n-1). If yes, why? A 64 bit computer word equals 8.
deadwind's user avatar
4 votes
4 answers
52k views

Converting columns from NVARCHAR to VARCHAR

I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns, so I wanted to look ...
equipe9's user avatar
  • 137
7 votes
1 answer
13k views

CLOB vs Varchar2 performance in this case?

From java application, I need to store the string which can be more than 4000 characters. I just need to store it. Then person having DB access can query and view it. That column won't be accessed ...
user3198603's user avatar