Questions tagged [varchar]

Normally refers to a variable length string data type.

Filter by
Sorted by
Tagged with
0 votes
1 answer
26 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 ...
user avatar
1 vote
1 answer
16 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 ...
user avatar
0 votes
0 answers
39 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 ...
user avatar
2 votes
1 answer
174 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,...
user avatar
4 votes
1 answer
294 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 ...
user avatar
  • 191
1 vote
1 answer
156 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": ["...
user avatar
  • 331
2 votes
0 answers
64 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 ...
user avatar
  • 121
5 votes
2 answers
1k 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 ...
user avatar
0 votes
2 answers
175 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 ...
user avatar
  • 59
1 vote
3 answers
805 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 ...
user avatar
1 vote
2 answers
2k 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)?
user avatar
  • 35
0 votes
1 answer
175 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 ...
user avatar
0 votes
1 answer
140 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 ...
user avatar
  • 91
0 votes
4 answers
2k 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) ...
user avatar
  • 737
4 votes
2 answers
410 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 ...
user avatar
1 vote
1 answer
461 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(...
user avatar
  • 266
1 vote
1 answer
147 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 ...
user avatar
  • 135
2 votes
2 answers
79 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 ...
user avatar
2 votes
1 answer
1k 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 ...
user avatar
  • 135
4 votes
2 answers
631 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 ...
user avatar
0 votes
1 answer
106 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?
user avatar
1 vote
2 answers
88 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 ...
user avatar
  • 123
0 votes
1 answer
123 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 ...
user avatar
15 votes
5 answers
12k 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 ...
user avatar
  • 7,100
0 votes
2 answers
86 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 ...
user avatar
6 votes
1 answer
116 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 ...
user avatar
16 votes
2 answers
3k 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. ...
user avatar
  • 271
3 votes
2 answers
3k 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 ...
user avatar
15 votes
1 answer
5k 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 ...
user avatar
  • 21.2k
0 votes
2 answers
313 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 ...
user avatar
  • 105
0 votes
1 answer
458 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. ...
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.) ...
user avatar
0 votes
2 answers
1k 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 ...
user avatar
  • 103
3 votes
2 answers
4k 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)=...
user avatar
  • 1,284
3 votes
1 answer
10k 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 ...
user avatar
1 vote
1 answer
143 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 ...
user avatar
  • 13
1 vote
0 answers
470 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.
user avatar
3 votes
4 answers
36k 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 ...
user avatar
  • 127
5 votes
1 answer
10k 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 ...
user avatar
15 votes
1 answer
16k views

What are the current best practices concerning varchar sizing in SQL Server?

I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives. Performance From my research, it seems that varchar(max) should ...
user avatar
-1 votes
1 answer
83 views

Text storage for mysql

So I want to create a command like first_name VARCHAR(30) NOT NULL, ... however I want the database to hold more characters like a text storage type. How would I set up the command for the text type?
user avatar
0 votes
1 answer
72 views

Does large VARCHAR take up more room in the transaction log

On SQL Server, would VARCHAR(500) take up more space in the transaction log than VARCHAR(100) when all the rows have no more than 100 characters?
user avatar
1 vote
1 answer
107 views

Is there any slowdown in casting from varchar to text?

In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and ...
user avatar
  • 55.7k
1 vote
2 answers
1k views

varchar(255) field or hash field?

To consider a table with millions records and the table schema: CREATE TABLE `foos` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `foo` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
user avatar
  • 143
-1 votes
1 answer
44 views

What are the benefits or clerks of using varchars to store states over booleans in mysql

We need to store some fields like state where we want to store things like published, waiting-moderation, or a field visibility where we want to store things like visible, private, private-with-...
user avatar
  • 101
3 votes
3 answers
6k 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 ...
user avatar
0 votes
1 answer
525 views

Does Oracle do a native conversion to numbers for varchar2 that are aggregated?

I have a table in an Oracle 11g database that's similar to the following: CREATE TABLE BUDGET_DATA ( BUDGET_MONTH varchar2(30) , BUDGET_CATEGORY varchar2(30) , BUDGET_AMOUNT varchar2(30) ) All ...
user avatar
  • 221
0 votes
1 answer
44 views

Why the length of JOBHISTORY field is 172 here?

It should be 150. Is Varray allocating some extra space?
user avatar
48 votes
1 answer
100k views

Size limit of character varying Postgresql

What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true? What are the valid sizes for ...
user avatar
4 votes
1 answer
3k views

Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

In an Twitter exchange, Simon West asks to Brandur, Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before ...
user avatar
  • 55.7k