logo
down
shadow

How to allow letters only in VARCHAR2 column (Oracle SQL)


How to allow letters only in VARCHAR2 column (Oracle SQL)

By : Krasimir Krustev
Date : November 19 2020, 01:01 AM
wish helps you You need to have a CHECK constraint in your table.
The following should suffice CHECK (regexp_like(surname,'^[[:alpha:]]+$')). Where [[:alpha:]] is alphabet character class. Thus, only considers the alphabets.
code :
SQL> CREATE TABLE TEST(
  2  surname VARCHAR2(10),
  3  CONSTRAINT constraint_name CHECK (regexp_like(surname,'^[[:alpha:]]+$'))
  4  )
  5  /

Table created.

SQL>
SQL> INSERT INTO TEST values ('LALIT')
  2  /

1 row created.

SQL>
SQL> INSERT INTO TEST values ('123')
  2  /
INSERT INTO TEST values ('123')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.CONSTRAINT_NAME) violated


SQL>
SQL> INSERT INTO TEST VALUES ('LALIT123')
  2  /
INSERT INTO TEST VALUES ('LALIT123')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.CONSTRAINT_NAME) violated


SQL>
SQL> SELECT * FROM TEST
  2  /

SURNAME
----------
LALIT

SQL>


Share : facebook icon twitter icon
Oracle: Find sum of a VARCHAR2(50 byte) column

Oracle: Find sum of a VARCHAR2(50 byte) column


By : Asha
Date : March 29 2020, 07:55 AM
I wish did fix the issue. As I mentioned the data in the VARCHAR column had a format like 4.900,25 where dot was hundred separator and comma decimal separator (2 decimal digits).
So to find the sum, I used TO_NUMBER function with some more parameters.
What is an efficient way to bulk copy data from a CLOB column to a VARCHAR2 column in Oracle

What is an efficient way to bulk copy data from a CLOB column to a VARCHAR2 column in Oracle


By : user1512121
Date : March 29 2020, 07:55 AM
it helps some times Consider using an INSERT INTO SELECT to create the new table on the fly with a new name, then add the indexes after creating the table, drop the old table, and rename the new table to the old name.
It's a DML operation, so it will be significantly faster, and also isn't slowed down by server logging settings.
Storing timestamp values stored in a varchar2 column into a date column in oracle

Storing timestamp values stored in a varchar2 column into a date column in oracle


By : user1718389
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I am assuming that you were trying to use TO_DATE on your text timestamp data. This won't work, because Oracle dates do not store anything more precise than seconds. Since your timestamps have fractional seconds, you may use TO_TIMESTAMP here, then cast that actual timestamp to a date:
code :
SELECT
    CAST(TO_TIMESTAMP('2018-01-12 16:13:51.100000',
                      'YYYY-MM-DD HH24:MI:SS.FF') AS DATE)
FROM dual;

12.01.2018 16:13:51
Insert superscript letters into VARCHAR2 column specifically trademark symbol

Insert superscript letters into VARCHAR2 column specifically trademark symbol


By : Asif Jamil Chowdhury
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , You cannot.
WE8ISO8859P15 is the Oracle identifier for ISO-8859-15. That encoding that does not have the ™ symbol (aka U+2122 'TRADE MARK SIGN').
How can one get raw byte values into a varchar2 column in oracle?

How can one get raw byte values into a varchar2 column in oracle?


By : Louis Hunninck
Date : March 29 2020, 07:55 AM
wish helps you What's not explained in the docs is that, according to UTL_RAW, a characterset is made up of 3 things; the NLS_LANGUAGE, the NLS_TERRITORY and the characterset itself. To see a list of valid values you can query V$NLS_VALID_VALUES. The full list of languages and territories is also available in the documentation.
This throws up the first of your problems. According to Oracle Win-1254 is not CP1254 but TR8MSWIN1254. Equally, although the UTF8 characterset exists I suspect your database is set up using AL32UTF8. You can double check by querying NLS_DATABASE_PARAMETERS.
Related Posts Related Posts :
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to convert multiple rows to one row in SQL Server?
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • Total sales amount for customers in cities
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org