A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [varchar]

Normally refers to a variable length string data type.

Filter by
Sorted by
Tagged with
0
votes
1answer
43 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 ...
2
votes
2answers
48 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 ...
0
votes
0answers
16 views

Postgresql - should character varying be limited if its going to be unique indexed?

For a column storing email, the length intended to be stored is never going to be over 254 characters. According to \d I currently have character varying (withOUT parentheses denoting a limit). Psql ...
2
votes
1answer
24 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 ...
4
votes
2answers
536 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 ...
1
vote
2answers
58 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 ...
0
votes
1answer
46 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 ...
15
votes
5answers
6k 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 ...
0
votes
2answers
27 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 ...
6
votes
1answer
101 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 ...
16
votes
2answers
1k 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. ...
3
votes
2answers
680 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 ...
12
votes
1answer
1k 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 ...
0
votes
1answer
209 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
199 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
678 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
394 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
1k 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
2k 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
26 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
110 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.
2
votes
4answers
12k 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 ...
3
votes
1answer
3k 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 ...
11
votes
1answer
6k 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
45 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
48 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
2k 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
69 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
368 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-...
3
votes
3answers
4k 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
192 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
38 views

Why the length of JOBHISTORY field is 172 here?

It should be 150. Is Varray allocating some extra space?
20
votes
1answer
44k 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 ...
3
votes
1answer
2k 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 ...
21
votes
3answers
19k 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 ...
4
votes
1answer
564 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 ...
12
votes
2answers
14k 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
37 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
3k 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
236 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
3k 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
89 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 ...
6
votes
3answers
7k 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
2k 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 ...
3
votes
5answers
3k 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
300 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
2k 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
144 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 ...