Stack Overflow
Stack Overflow

Questions tagged [varchar2]

The VARCHAR2 datatype stores variable-length character strings.

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

192 questions
0
votes
3 answers

Convert String to Decimal in pl sql

I want to convert a string to a number with one decimal place. Ex: 4 after conversion -> 4.0 4.1 after conversion -> 4.1 04.1 after conversion -> 4.1 04.0 after conversion -> 4.0 I have tried SELECT TO_NUMBER('04.0','99.99') FROM…
Thilina De Silva
  • 391
  • 2
  • 13
  • 25
0
votes
1 answer

How to exclude rows with varchar2 value that is not null, but exports as null

I have some data, which I cannot change, in a varchar2(800) column, which displays in SQL Developer as a null and exports from there as a null, but is not null. Here's what I tried: length(mycol) return 3 asciistr(mycol) returns null substr(mycol,…
Bohemian
  • 412,405
  • 93
  • 575
  • 722
0
votes
1 answer

ORACLE Obtain the greater and lower value in a varchar2 field range

I have a table with a field varchar2 type, Suppose this values: aaab s123 2445 25 21000 2500000 1 10000790 1899 I need to obtain the greater value that begins with 2. I was trying with: Select TO_NUMBER(myfield) from Services where myfield like…
joseluisbz
  • 1,491
  • 1
  • 36
  • 58
0
votes
1 answer

How to detect data type in column of table in ORACLE database (probably blob or clob)?

I have a table with a column in the format VARCHAR2(2000 CHAR). This column contained a row containing comma-separated numbers (ex: "3;3;780;1230;1;450.."). Now the situation has changed. Some rows contain data in the old format, but some contain…
Michael Varfolomeev
  • 1
  • 3
0
votes
1 answer

Difference between CHAR and VARCHAR2 in PL/SQL function parameters

Let's consider the four functions with these signatures: FUNCTION my_func_1(pi_param CHAR) RETURN CHAR ... FUNCTION my_func_2(pi_param CHAR) RETURN VARCHAR2 ... FUNCTION my_func_3(pi_param VARCHAR2) RETURN VARCHAR2 ... FUNCTION my_func_4(pi_param…
diziaq
  • 6,881
  • 16
  • 54
  • 96
0
votes
1 answer

Oracle-DB: Reliable way to calculate the length of a field in CHAR from all_tab_columns.data_length?

I've written a very simple database access layer that maps C# objects onto database tables and back. It uses the information in all_tab_columns in order to do some input validation (mainly length checking). It works ok on ISO-encoded databases but…
Christopher Thonfeld-Guckes
  • 306
  • 5
  • 16
0
votes
1 answer

how to solve Data type BLOB can not be converted to varchar2

I have created in apex 5.1 a report with form in oracle apex 5.1 in which I have a BLOB column called 'LIEN'. And when I insert data in the table and run the application I get this error: Data type BLOB can not be converted to VARCHAR2! How can…
YOUCEF sami
  • 29
  • 8
0
votes
0 answers

SQL Query Fails for some records

I'm running a query where I'm selecting records that contain a word in a VarChar2(4000) field. The query works but only for some dates and not others. I've tried REGEXP_LIKE and INSTR. I've tried it in SQL Developer and ACCESS and I get the same…
LifHkr
  • 35
  • 5
0
votes
1 answer

Searching VARCHAR2 in oracle 11g DB

I am seraching varchar2 column "DF_FORM_COMP_VALUE" that includes ID Number with address to retrieve data by searching according to the ID Number only in oracle 11g DB. I built the following code to retrieve the data using System; using…
Maxy626
  • 3
  • 2
0
votes
2 answers

Oracle Apex 999 characters limitation

I am using Oracle Apex Express 5.1.1.00.08. I have enough room on my table spaces. My workspace has currently 25 database applications and a total file size of 12 MB. I know at some point in the past populating a varchar2(4000) variable worked well.…
Remus Sherbourne
  • 1
  • 1
0
votes
0 answers

Can I dynamically initialize a associative array indexed by varchar with null to avoid referencing an empty element

I have a situation where I want to have a collection, actually in this case it is a nest collection (Associative array) that I would like to index by varchar so that I can access it by a field element instead of iterating to find a value. The only…
programmerNOOB
  • 121
  • 3
  • 19
0
votes
2 answers

Error converting varchar to numeric (but there's no number)

I have a table with several columns, like this: CREATE TABLE CRM.INFO_ADICIONAL ( ID_INFO_ADICIONAL NUMBER(10) NOT NULL, NOMBRE VARCHAR2(100 BYTE) NOT NULL, OBLIGATORIO NUMBER(1) NOT…
Hugo Lisandro Puyol
  • 1
  • 5
0
votes
1 answer

Separating field into multiple columns XML/Varchar2/SQL

I am working with a database that pulls data in from an application. The data appears as XML in the field, but the datatype is actually varchar2. How do I split the data into columns by the names in the brackets? So end result would be each name in…
Lindsey Jackson
  • 3
  • 3
0
votes
1 answer

PLSQL - Convert Char Array To Varchar2

I have a function that remove last character from a varchar2, but I needed to convert it to char array first. Now I cant find anything to convert it back to varchar2. My function: DECLARE TYPE CHAR_ARRAY IS TABLE OF CHAR(1) INDEX BY…
Adolfok3
  • 5
  • 1
  • 5
0
votes
2 answers

Grouping by date stored as varchar2

I am trying to run the following query: SELECT TAB.week_end, COUNT(TAB.id) FROM ( SELECT C.week_end, C.ID FROM my_table C WHERE C.week_end IS NOT NULL ) TAB GROUP BY TAB.week_end,…
xcelm
  • 541
  • 1
  • 6
  • 19
1 2 3
…
12 13