Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [varchar]

Normally refers to a variable length string data type.

0
votes
1answer
18 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 ...
0
votes
1answer
19 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. ...
1
vote
2answers
50 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.) ...
0
votes
2answers
62 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 ...
3
votes
2answers
445 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)=...
1
vote
1answer
370 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 ...
0
votes
1answer
17 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 ...
1
vote
0answers
63 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.
0
votes
2answers
2k views

Converting 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 ...
2
votes
1answer
931 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 ...
7
votes
1answer
2k 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 ...
-1
votes
1answer
36 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?
0
votes
1answer
47 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?
1
vote
1answer
897 views

How to save a varchar(MAX) to a file?

For my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
1
vote
1answer
48 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 ...
1
vote
2answers
86 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, ...
-1
votes
1answer
35 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-...
2
votes
3answers
2k 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 ...
0
votes
1answer
69 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 ...
0
votes
1answer
37 views

Why the length of JOBHISTORY field is 172 here?

It should be 150. Is Varray allocating some extra space?
5
votes
1answer
13k views

Size limit of character varying Postgresql

What is size limit of data type characters in postgresql, found that character varying(n), varchar(n) is 1 to 10485760 this is really? But the size limit of data type character(n), char(n) and text ...
1
vote
1answer
823 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 ...
18
votes
3answers
11k views

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
3
votes
1answer
374 views

What happens when you update a VARCHAR cell with a longer string?

Say I have a column of type VARCHAR(32) in a mySQL database. On Spetmeber 1st I decide to store the string "tea", but on September 2nd I decide to update it to "coffee". Clearly, if all the records ...
11
votes
2answers
7k views

Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

From this Microsoft doc,+ n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual ...
0
votes
1answer
35 views

How to avoid typecasting leading to full table update? [duplicate]

Is there a way by which update to ALL rows of a Mysql table could be denied or avoided? Background: I have a table 'Orders' having 'OrderID' (Type VARCHAR) and 'VendorID' (Type Integer) as two of ...
2
votes
3answers
2k views

How to change VARCHAR length in PRIMARY KEY in SQL Server 2014

I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is ...
1
vote
0answers
154 views

Oracle 11.2 index on VARCHAR vs index on DATE

I have a table with 15,339,375 records and it has an index on a date field that's converted to a VARCHAR(5) and stored as "MMYDD". It has an index on the field. The records in the table are all from ...
0
votes
2answers
2k views

the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?

I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all. the way I have been doing this is demonstrated ...
1
vote
2answers
73 views

Indexing a string by a subset of its characters (i.e.: the Kanji ones)

I have a database in PostgreSQL modelling a Japanese->English dictionary. The tables I am interested in are listed as follows, where bold represents PK and italic FK: entries(entry_id int, keyword ...
5
votes
3answers
2k views

Switch VARCHAR to DATE in MySQL

I inherited a database that has a VARCHAR column that really should be a DATE. There's no checking on the form (I need to fix that) so dates constantly get entered as 2016-12-4 instead of 2016-12-04 ...
1
vote
1answer
1k views

Tool for converting all VARCHAR to NVARCHAR in one or few steps

I am using SQL Server Management Studio and need to convert all VARCHAR columns to NVARCHAR. Is there a tool or something similar to do the conversion in one or few steps? It should be applied to ...
2
votes
5answers
2k views

How to prevent duplicate VARCHAR without a key limit?

I'd like to store URLs in a database column, and enforce a constraint that values must be unique. Unfortunately, MySQL has a limit on the length of index keys which means that only the first X ...
0
votes
1answer
200 views

Return result set as string

I have a table with SELECT fName, lName FROM person It returns 3 results I want to return a concat(fName, ' ', lName) and 2 "and"s in between each concat. Jon Snow and Margaery Tyrell and Peter ...
0
votes
1answer
1k views

ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50)

I am importing a csv file into an Oracle database using an external table. The file encoding is UTF-8. The separator is actually ;. The target table definition is identical to the external table ...
1
vote
2answers
1k views

Remove both Special Characters and String Characters from output SQL Server

I am looking to remove some string characters in an output that I have so that it only shows numbers rather then letters and numbers. My Query is: Select DISTINCT t.processed 'Title', (Select max(...
1
vote
3answers
115 views

Raw cast of arbitrary string to integer [closed]

I have non-numeric string IDs in a table, and would like to cast them to unique integers. I need the mapping to be invertible / one-to-one. The string IDs are of uniform length. I want to do this by ...
1
vote
2answers
169 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
3
votes
1answer
661 views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
22
votes
2answers
712 views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
4
votes
2answers
2k views

Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells?

Do varchar/text/longtext columns in MySQL occupy full length size in filesystem even for incomplete cells? Referring to this blog article for example I have the following columns and i have put in a ...
1
vote
1answer
1k views

How large space will be occupied by mysql for a varchar utf8 column?

Tip: To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible length....
11
votes
4answers
8k views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
13
votes
1answer
3k views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
-1
votes
1answer
3k views

How To Declare Fixed Length String Value In PL/SQL

In PL/SQL strings are declared in two different ways. These ways are fixed-length strings declaration, variable-length strings declaration. If we need to declare a variable-length string, we must ...
26
votes
2answers
1k views

Why does LEN() function badly underestimate cardinality in SQL Server 2014?

I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is ...
0
votes
1answer
504 views

Convert ASCII Bytes from XML to VARCHAR

I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried: DECLARE @testxml XML = N' <...
0
votes
1answer
77 views

Can I use LongText instead of varchar

I want to store string like ins37_14387771184402,ins37_14387771184403,ins37_14387771184404,ins37_14387771184405,ins37_14387771184402,ins37_14387771184403,ins37_14387771184404,ins37_14387771184405,...
2
votes
1answer
3k views

Oracle Performance - Varchar2 (x char) x Varchar2 (x byte)

I know the definition diference between Varchar2 (x char) x Varchar2 (x byte). varchar2(20 char) means you can store 20 characters -- whereas varchar2(20) means you can store 20 bytes. What about ...
1
vote
1answer
120 views

Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?

Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?