Home
Search results “Length of a string oracle”
LENGTH Function in SQL Query
 
02:33
Length(): This function is used to calculate the length of a string or column in query. Here space is also treated as one more character. so the count is 10. Queries used in Video: select ename,length(ename) from emp; select length('oracle num') from dual; Thanks for watching..:)
Views: 2281 WingsOfTechnology
length, concat & chr | sql functions | oracle database 11g version 2 |
 
05:24
executed in oracle database 11g version 2
Views: 150 Education 4u
Oracle Database11g tutorials 13 || SQL substr function / SQL substring function
 
06:34
link of SQL substr blog : http://www.rebellionrider.com/SQL-substr-function.htm SQL substr function SQL Substr function will return a sub string of a specified length from the source string beginning at a given position. Tool used in this tutorial is command prompt. This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training. Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=j7eYO7 Contacts E-Mail [email protected] Twitter https://twitter.com/rebellionrider Instagram http://instagram.com/rebellionrider Facebook. https://www.facebook.com/imthebhardwaj Linkedin. http://in.linkedin.com/in/mannbhardwaj/ Thanks for linking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ---------------------------------------------------------------------------- As the name suggests SQL Substr function will return substring from a given source string. Let's see the Syntax Substr (source_string, start_pos, Substr_length) As we can see SQL substr function takes 3 parameters. First one is Source string from which you want to extract the segment. Second parameter is Starting position for sub string from the Source string. And the third parameter is Substr_length which is the length for the substring. First two parameters are mandatory to specify while third one is optional. So we can say. SQL Substr function will return a sub string of a specified length from the source string beginning at a given position. First parameter source string can be of any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB whereas both start_pos, Substr_length parameters must be number data type. The returning result of SQL Substr function is of same data type of source string. Let's see an example of SQL Substr function. SELECT substr('www.RebellionRider.com',5,14) FROM dual; Here in this query url of my website www.RebellionRider.com is our source string with the total length of 22 characters, Now, I want to extract the name of my website that is, RebellionRider. So if you count the total length of the name of the website, it is 14. That's why I have specified 14 as my third parameter of SQL Substr function which is substr_length. Also the name of the website RebellionRider is starting from 5th position therefore I have specified 5 at second parameter of SQL substr function which is strt_pos or starting position. Execute it. Here is our result RebellionRider When starting position is larger than the length of source string. In this case SQL Substr function will return NULL as a result. Let's do an example. SELECT substr('www.RebellionRider.com',23,14) FROM dual; As you can see here I have specified 23 at starting position and the total length of our source string is 22 characters Let's execute And the result is Null. Second scenario When the Substr_length is greater than source string In this case the segment return is the substring from starting position to the end of the string. For example SELECT substr('www.RebellionRider.com',5,23) FROM dual; Our starting position is at 5 means at the first R of RebellionRider and length of substring is set to 23 which is greater than the length of source string that is 22. Execute. As you can see we get a substring from first R of RebellionRider till the end of the source String. Third scenario When you supply numeric or arithmetic expression or a DATE instead of character as Source string to SQL Substr function In this scenario If you have supplied a numeric string instead of character as source string, the oracle engine casts them as a character when they occur as parameter to SQL Substr function. And if you have supplied Arithmetic expression or a DATE then The Oracle engine first solves or evaluates the Arithmetic expression or the DATE Then casts them as a character. Means if you have arithmetic expression in your source string then oracle will first solve it and then change or say cast the value of its result into character. Let's see some example. SELECT substr(50000-7,2,4) FROM dual; Oracle first evaluates the arithmetic expression that is 50000-7 equals to 49993. And then oracle engine casts this result 49993 into a character string. Means 49993 will be a 5 characters string. Starting position of substring is 2, that means from the first 9 of 49993 We specified the length of substring is 4 so we must get 9993 as our result. Let's check execute
Views: 81911 Manish Sharma
PL/SQL: Without using Length function
 
06:22
In this tutorial, you'll learn how to get the length of a string PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 2158 radhikaravikumar
SQL Tutorial - 42: The LENGTH() & INSERT() Functions
 
04:40
In this tutorial we'll learn to determine the length of strings and also to insert strings within other strings through the LENGTH() and INSERT() functions respectively.
Views: 21161 The Bad Tutorials
Oracle || String functions Part-1 by dinesh
 
36:42
DURGASOFT is INDIA's No.1 Software Training Center offers online training on various technologies like JAVA, .NET , ANDROID,HADOOP,TESTING TOOLS ,ADF,INFORMATICA,TABLEAU,IPHONE,OBIEE,ANJULAR JS, SAP... courses from Hyderabad & Bangalore -India with Real Time Experts. Mail us your requirements to [email protected] so that our Supporting Team will arrange Demo Sessions. Ph:Call +91-8885252627,+91-7207212428,+91-7207212427,+91-8096969696. http://durgasoft.com http://durgasoftonlinetraining.com https://www.facebook.com/durgasoftware http://durgajobs.com https://www.facebook.com/durgajobsinfo......
SQL tutorial 71: LPAD and RPAD SQL Function By Manish Sharma RebellionRider
 
03:35
Learn the step by step concepts of Lpad and Rpad SQL functions In oracle Database with example By Manish Sharma RebellionRider ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/lpad-and-rpad-sql-functions Previous Tutorial ►Tut 10 SQL Single Row Function https://youtu.be/5rx8Q4x4-qI ►Tut 11 case and character manipulation functions https://youtu.be/243Pqbta7oI ►Tut 12 SQL Concat Function https://youtu.be/De8UvHro5UY ►Tut 13 SQL substr function https://youtu.be/f52uKYyYFoU ------------------------------------------------------------------------- ►►►Let's Get Free Uber Cab◄◄◄ Use Referral Code UberRebellionRider and get $20 free for your first ride. ------------------------------------------------------------------------- ►►►Help Me In Getting A Job◄◄◄ ►Help Me In Getting A Good Job By Connecting With Me on My LinkedIn and Endorsing My Skills. All My Contact Info is Down Below. You Can Also Refer Me To Your Company Thanks ------------------------------------------------------------------------- Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage https://copy.com?r=kb4rc1 -------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! -------------------------------------------------------------------------- Amazon Wishlist: http://bit.ly/wishlist-amazon ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider https://www.facebook.com/imthebhardwaj http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ http://rebellionrider.tumblr.com/ http://www.pinterest.com/rebellionrider/ You can also Email me at for E-mail address please check About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 21592 Manish Sharma
mod, power  sqrt & trunc | sql functions | oracle database 11g version 2 |
 
07:19
executed in oracle database 11g version 2
Views: 212 Education 4u
Oracle INSTR Function
 
03:28
https://www.databasestar.com/oracle-instr/ The Oracle INSTR function allows you to search a string for the occurrence of another string, and return the position of the occurrence within a string. It’s helpful for finding if a string exists within another string. It can also be used for performing further string manipulation on, like substrings. The syntax of the INSTR function is: INSTR(string, substring [, start_position [, occurrence]]) These parameters are: string: The text string that is searched in. It’s usually the larger of the two strings. Mandatory. substring: The text to search for. It’s usually the smaller of the two strings. Mandatory. start_position: This is an integer value which indicates where in the string value to start the search. Optional, and the default is 1. occurrence: The occurrence of the substring to search for. Optional, and the default is 1, which means the first occurrence. Also, the searches performed by the INSTR function are case-sensitive. The value returned by INSTR is a number value, which is the number in the location of the string where the substring is found. The first character is 1. For more information about the Oracle INSTR function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-instr/
Views: 5121 Database Star
Oracle SQL Generate Random Alphanumeric String
 
07:21
In this video, you’ll learn how to generate random alphanumeric characters in an SQL statement in Oracle SQL You can use Oracle SQL to generate random alphanumeric string values by using one of the built-in functions. This function allows you to specify the type of string to generate - upper or lowercase alpha characters only, mixed alpha characters, uppercase alphanumeric characters, or any printable characters. You can also specify the length of the string to generate, This can be a fixed number, or you can use another function to generate a random number to use for the length! You’ll see some examples of generating random alphanumeric string values in this video. For more information and tips on Oracle SQL, check out DatabaseStar.com: https://www.databasestar.com/
Views: 450 Database Star
Oracle TO_DATE Function Explained with Examples
 
06:14
https://www.databasestar.com/oracle-to_date/ The Oracle TO_DATE function is one of the most useful conversion functions in Oracle. It can be a little confusing though. The TO_DATE function is used to convert a character value to a date value. You can use it to convert a string that is in the format of a date, into a date data type. Why would you use this? Converting a character to a date is helpful if you want to insert a date into a column in a table. Being specific about the format (explicit) is better than assuming the conversion will be done for you (implicit). This TO_DATE function only converts to a DATE data type. If you want to convert to the other datetime data types, you’ll have to use other functions such as TO_TIMESTAMP or TO_TIMESTAMP_TZ. The syntax for this function is: TO_DATE(charvalue [, format_mask [, nls_date_language]]) The parameters for this function are: Charvalue: This is the character value that you want to convert to a date. It’s the main input of the function. Format_mask: This is the format that the input value, the charvalue, is in. This is used to help convert your character value into a date, as your character may be in a different format to the default. Nls_date_language: this is used to determine how the output is displayed. For more information about the Oracle TO_DATE function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-to_date/
Views: 1771 Database Star
Creating Variable Length Lines
 
02:29
This video is one of many videos series produced by © Hosny Corporation System http://www.hosnysys.com
Views: 206 Mohamed Hosny
Find the Number of Occurrences of a Character In the given String SQL | ORACLE | PL SQL
 
03:34
Find the Number of Occurrences of a Character In the given String SQL | ORACLE | PL SQL
Views: 1039 Online Pl Sql
Oracle Tutorial for Substr and Instr functions
 
09:19
This video explains how Oracle SubStr and Instr function works
Views: 5240 Ganesh Anbarasu
Tutorial#43  SUBSTR function in Oracle SQL Database| How to get substring from String/column
 
10:23
SQL Substr function will return a substring of a specified length from the source string beginning at a given position. or SQL find a character in a string or how to make use of a substr function or what is a substring function in Oracle Assignment link: https://drive.google.com/open?id=1q7WCcP-nb-kdVyRzqWXJE9hnLW2M-EMH In this series we cover the following topics: SQL basics, create table oracle, SQL functions, SQL queries, SQL server, SQL developer installation, Oracle database installation, SQL Statement, OCA, Data Types, Types of data types, SQL Logical Operator, SQL Function,Join- Inner Join, Outer join, right outer join, left outer join, full outer join, self-join, cross join, View, Subquery, Set Operator. follow me on: Facebook Page: https://www.facebook.com/LrnWthr-319371861902642/?ref=bookmarks Contacts Email: [email protected] Instagram: https://www.instagram.com/equalconnect/ Twitter: https://twitter.com/LrnWthR #SubstrFunction #SubstringFunctionSQL #OracleSubtr
Views: 223 EqualConnect Coach
Strings in PL SQL Tutorial
 
07:35
Subscribe for more PL SQL tutorials https://www.youtube.com/channel/UC7sbHUgN8FnJEZkEjvKTwJg String Function & Purpose 1 ASCII(x); Returns the ASCII value of the character x. 2 CHR(x); Returns the character with the ASCII value of x. 3 CONCAT(x, y); Concatenates the strings x and y and return the appended string. 4 INITCAP(x); Converts the initial letter of each word in x to uppercase and returns that string. 5 INSTR(x, find_string [, start] [, occurrence]); Searches for find_string in x and returns the position at which it occurs. 6 INSTRB(x); Returns the location of a string within another string, but returns the value in bytes. 7 LENGTH(x); Returns the number of characters in x. TUTORIALSPOINT Simply Easy Learning Page 59 8 LENGTHB(x); Returns the length of a character string in bytes for single byte character set. 9 LOWER(x); Converts the letters in x to lowercase and returns that string. 10 LPAD(x, width [, pad_string]) ; Pads x with spaces to left, to bring the total length of the string up to width characters. 11 LTRIM(x [, trim_string]); Trims characters from the left of x. 12 NANVL(x, value); Returns value if x matches the NaN special value (not a number), otherwise x is returned. 13 NLS_INITCAP(x); Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT. 14 NLS_LOWER(x) ; Same as the LOWER function except that it can use a different sort method as specified by NLSSORT. 15 NLS_UPPER(x); Same as the UPPER function except that it can use a different sort method as specified by NLSSORT. 16 NLSSORT(x); Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used. 17 NVL(x, value); Returns value if x is null; otherwise, x is returned. 18 NVL2(x, value1, value2); Returns value1 if x is not null; if x is null, value2 is returned. 19 REPLACE(x, search_string, replace_string); Searches x for search_string and replaces it with replace_string. 20 RPAD(x, width [, pad_string]); Pads x to the right. 21 RTRIM(x [, trim_string]); Trims x from the right. 22 SOUNDEX(x) ; Returns a string containing the phonetic representation of x. 23 SUBSTR(x, start [, length]); Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied. 24 SUBSTRB(x); Same as SUBSTR except the parameters are expressed in bytes instead of characters for the single-byte character systems. 25 TRIM([trim_char FROM) x); Trims characters from the left and right of x. 26 UPPER(x); Converts the letters in x to uppercase and returns that string.
Views: 394 Puzzle Guru
Session6 Data type in Oracle
 
15:25
Session 6: Datatypes In Oracle   ALPHABET           : A-Z , a-z NUMBER              : 0-9 (with precision and scale) DATE / Temporal  : any Date and time (Hours, Minutes, Seconds, Mili-seconds, Timestamp, Timezone etc)   Alphabet + Number = Alphanumeric Data                                     = String / CHARACTER Datatype Category Number                    =  Numeric Datatype Category Date                         = Date Datatype Category 1. CHARACTER Datatype: CHAR, VARCHAR, NCHAR: CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9 , all keyboard characters etc. The default size is 1 character and it can store maximum up to 2000 bytes. Example : EName, EmpID, PassportNo, SSN, etc. EName CHAR(10) := ‘TOM’; wastage of 7 space after the string EName VARCHAR(10) := ‘TOM’; Spaces can be Reuse which left after the string NCHAR additionally handles NLS(National Language Support). Oracle supports a reliable Unicode datatype through NCHAR , NVARCHAR2 , and NCLOB  VARCHAR2, NVARCHAR2: These are Variable length datatype. VARCHAR2 handles alphanumeric character string whereas NVARCHAR2 handles alphanumeric character string with NLS(National Language Support). The default size is 1 character and it can store maximum up to 4000 bytes.   LONG:  Variable length string.  (Maximum size: 2 GB - 1) Only one LONG column is allowed per table. RAW:    Variable length binary string (Maximum size 2000 bytes) LONG RAW: Variable length binary string (Maximum size 2GB) 2. NUMERIC Datatype: NUMBER: It stores Numeric values and performs numeric calculations. NUMBER,   NUMBER(n),   NUMBER(p,s) It stores Numbers up to 38 digits of precision. SeqNo NUMBER;                     1, 123, 12345678 EmpID NUMBER(4);                 1, 123, 1234 Sal NUMBER(7,2);                     23456.78 , 123.45 — correction in video: Sal NUMBER(a7,2); which is wrong please ignore. 1234567 can be a type of NUMBER, NUMBER(7), NUMBER(7,0) It can store both integer and floating point numbers NUMERIC(p,s) FLOAT:   Ex:  EmpSal FLOAT;    FLOAT(7)       Decimal Points allowed DEC(p,s), DECIMAL(p,s) , REAL, DOUBLE PRECISION INTEGER:   Ex:  SSN INTEGER;       Decimal Points are not allowed INT, SMALLINT 3. DATE Datatype: DATE: It stores DATE(Date, Month, Year) and Time(Hour, Minute, Second, AM/PM) and performs calculations with such data. Default DATE format in Oracle is “DD-MON-YY” Based on "Gregorian calendar" where the date ranges from “JAN 1 4712 BC” to “DEC 31 9999 AD” doj DATE;    “18-MAR-2010 12:30:00 PM” TIMESTAMP:    It can store all parameters as DATE datatype and additionally it can have “Fraction of seconds” and TIMESTAMP WITH TIMEZONE / TIMESTAMP WITHOUT TIMEZONE. Range from 0-9 digits, the default size is 6. 4. LOB Datatype: LOB: “Large Object” data. It can store pictures, motion pictures, Textfiles etc. CLOB: “Character Large Object” is used to store structured information like a text file with a specific file format. BLOB: “Binary Large Object” is used to store Un-structured information like Image, JPEG files, MPEG files etc. BFILE: “Binary File” is used to store the pointer to a specific file / Just store the location of a file. Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) Extra Information: NCLOB : It supports all the character set supported by CLOB and additionally it handles NLS(National Language Support ) Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) ROWID and UROWID(optional size) Datatype: contains fixed length Binary data. BBBBBBB.RRRR.FFFFF combination of BLOCK-ROW-DATABASE FILE Physical and Logical ROWID Upcoming Session: Session 7: Populating Data into Tables(INSERT Statement): Inserting data into all columns of a table Inserting data into Required columns of a table Inserting NULL value into a table Inserting Special Values(USER / SYSDATE) into a table Supplying data at runtime(using & and &&) THANK YOU :)
Views: 232 Prabhat Sahu
11-Oracle Database  CONCAT,SUBSTR, LENGTH, INSTR, LPAD,RPAD, TRIM, Round,Trunc,Mod.avi
 
33:16
11-Oracle Database - Intro to Types of Single Row Functions - CONCAT,SUBSTR, LENGTH, INSTR, LPAD,RPAD, TRIM, Round,Trunc,Mod.avi
Views: 4521 Adel Sabour
Tutorial 13 : SQL TRIM(), LTRIM() and RTRIM()
 
09:58
Hi Friends! Here we are learning about TRIM(), LTRIM() and RTRIM() function. Hope you'll understand about these functions. Let me know for any confusion or doubt. You can write me in comment box or can mail me. eMail Id - [email protected] Useful Tutorial Links : 1. SQL SINGLE ROW FUNCTION EXPLAINED https://youtu.be/VPtlTNVHDZc 2. SQL LENGTH() FUNCTION -- Length() Function Part-1 https://youtu.be/-EUd-CJLt0M -- Length() Function Part-2 https://youtu.be/I3gEsN_iYJo Thanks!
Views: 410 YourSmartCode
How to use trim, concat, instr, length in Oracle Sql
 
08:38
How to use trim, concat, instr, length in Oracle Sql
Views: 366 Tanmun
SQL 12c Tutorial 10  : SQL Single Row Character Functions
 
10:30
SQL 12c Tutorial 10 : SQL Single Row Character Functions SQL 12c Tutorial for beginners Oracle SQL 12c Tutorial Case Conversion Functions UPPER LOWER INITCAP Character Manipulation functions CONCAT LENGTH SUBSTR INSTR REPLACE TRANSLATE TRIM LTRIM RTRIM LPAD RPAD
Views: 288 TechLake
Hierarchical Queries in Oracle/ عربي
 
16:39
start with & connect by prior Hierarchical Queries in Oracle ملاحظه : اشارة الاكبر غير مسموحة في الوصف --1--- select employee_id, first_name,last_name,manager_id from employees; --2--- select employee_id, first_name,last_name,manager_id from employees where manager_id=100; --3--- select employee_id, first_name,last_name,manager_id from employees where manager_id=101; --4--- select employee_id, first_name,last_name,manager_id from employees where manager_id=108; --5--- select employee_id, first_name,last_name,manager_id from employees where manager_id in (109,110,111,112,113) --6--- select employee_id, first_name,last_name,manager_id from employees start with employee_id=100 connect by prior employee_id=manager_id --top down --7--- select employee_id, first_name,last_name,manager_id from employees start with employee_id=113 connect by prior manager_id =employee_id --buttom up --8--- --let us take this and try to do nice thing --Neena Kochhar work for Steven King select employee_id, first_name,last_name,manager_id from employees start with employee_id=100 connect by prior employee_id=manager_id --top down --9--- select first_name||' '||last_name ||' work for '|| (select first_name||' '||last_name from employees e where e.employee_id=a.manager_id ) from employees a start with employee_id=100 connect by prior employee_id=manager_id --top down --10--- select first_name||' '||last_name ||' work for '|| prior first_name||' '||prior last_name from employees start with employee_id=100 connect by prior employee_id=manager_id --top down --11--- select level,employee_id, first_name,last_name,manager_id from employees start with employee_id=100 connect by prior employee_id=manager_id --top down --1 mean root --2 mean child of root --3 mean child of child , and so on --12--- select level,employee_id, first_name,last_name,manager_id from employees where level=2 start with employee_id=100 connect by prior employee_id=manager_id --13--- select lpad ('khaled',10, 0) from dual --14--- select level, case when level=1 then first_name||' '||last_name when level Greater sign 1 then lpad (first_name||' '||last_name,length(first_name||' '||last_name)+level-1, '-') end tree from employees start with employee_id=100 connect by prior employee_id=manager_id --top down ---------------------- --15--- select employee_id, first_name,last_name,manager_id from employees start with employee_id=100 connect by prior employee_id=manager_id --top down --16--- select employee_id, first_name,last_name,manager_id from employees start with employee_id=100 connect by manager_id= prior employee_id --top down
Views: 2754 khaled alkhudari
Oracle DUMP Function
 
03:04
https://www.databasestar.com/oracle-dump/ The Oracle DUMP function tells you the data type of an expression you specify. Specifically, this function returns a string that explains what type of data it is, along with the length, and an internal representation. The syntax of the DUMP function is: DUMP ( expression [, return_format] [, start_position] [, length] ) The parameters of this function are: - expression: this is the expression that will be looked at and will have information displayed from this function. It can be a column or any other kind of expression. - return_format: this is the format of the data to return. - start_position: this is the starting position of the expression to return the internal representation for. Does not impact the data type shown. - length: this is the length from the start_position that the internal representation of the expression is shown for. The DUMP function returns a VARCHAR2 data type. For more information about the Oracle DUMP function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-dump/
Views: 104 Database Star
how to count length of field or data in sql server
 
08:31
with full hd for sql programming language
Views: 917 Sourn Sarim
Oracle LTRIM Function
 
03:06
https://www.databasestar.com/oracle-trim/ The Oracle LTRIM function allows you to remove a specific character from the left side of a string. It’s one of the most commonly used string manipulation functions in SQL, in my experience. The “L” in “LTRIM” stands for “left”. It’s often used to remove spaces from the left of a string. However it can be used to remove other characters, such as leading characters from URLs. The syntax for the LTRIM function is: LTRIM(input_string, [trim_character]) The input_string is the text you want to remove characters from. It can be any kind of expression, such as a specific text value or a column. The trim_character is the character you want to remove from the left of the string. It’s an optional parameter, and if you don’t specify it, then a space character is used by the function. The parameters can be any type of string. The function will return either a VARCHAR2 or a LOB data type, depending on the type of data you specify in the parameters. You can specify more than one value for the trim_character, and the LTRIM function will remove both characters from the left of the string. The function is similar to the RTRIM value, where RTRIM removes characters from the right of the string. For more information on the LTRIM function, including the SQL used in this video, read the related article here: https://www.databasestar.com/oracle-trim/
Views: 124 Database Star
Oracle SQL Tutorial 24 - Important Data Types
 
06:09
In the upcoming videos we are going to discuss data types in depth, but I don't want to drown you in all of the details. Because of that, I'm giving you this video which is going to introduce you to the most important data types. Then, in the upcoming videos, I'll describe them in more depth. One of the data types we've already discussed in this video is NUMBER. This data type is used to, obviously, store a number. It can be used to store integers (whole numbers), or numbers with decimals. There are two other numeric data types you should know of. BINARY_FLOAT and BINARY_DOUBLE are both numeric data types that are known as floating point numbers. A floating point number is often used for large numbers that have decimal places where it is acceptable to not be completely precise. What I mean by this is that these numbers can only store numbers correctly up to a certain decimal point. If you need perfect precision, you will want to use the NUMBER data type. Now storing numbers is good sometimes, but occasionally you will want to store string data. String data can be any sequence of characters, including numbers. By telling the database that a column is a string data type, the database knows how to treat that column. There are four important string data types that you need to know about. The first two are CHAR and NCHAR. These data types are used to store a fixed-length string. So for example, you can say you want to store 12 characters. This means that every value for this column will be exactly 12 characters. If you insert less than 12 characters, the data will be padded with spaces. This means you will want to use one of these data types when every value in the column is the same length. What is the difference between CHAR and NCHAR? CHAR uses what is known as ASCII while NCHAR uses Unicode. The difference is what characters are allowed and how much space each character takes. ASCII takes up less space but only supports English, numbers, and some symbols. UNICODE allows you to store characters from multiple languages but takes up more space. Those were both fixed-length string. What if you want to store data that changes in length? That is where VARCHAR2 and NVARCHAR2 come in. When it comes to storing dates, the data types that are most important are DATE and TIMESTAMP. Date can be used to store dates and time. Timestamp is a data type that can be used to store an exact moment in time. Lastly, there are interval types. These store a date range. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 7010 Caleb Curry
INSTR in Oracle | INSTRING in Oracle | Character Manipulation | Oracle Tutorial for Beginners
 
07:32
INSTR in Oracle | INSTRING in Oracle | Character Manipulation | Oracle Tutorial for Beginners INSTR function in Oracle INSTR function in Oracle SQL INSTR in oracle with example INSTR examples INSTR in Oracle INSTRING in Oracle Character Manipulation Oracle Tutorial for Beginners INSTR in Oracle INSTR in Oracle INSTR in oracle with example INSTRING in Oracle Oracle Tutorial for Beginners INSTR in oracle with example Oracle Database tutorials for Beginners INSTR INSTR INSTR in oracle with example regexp_substr in oracle regexp_instr in oracle oracle INSTRING before character instr in oracle oracle INSTRING right oracle length oracle sql INSTRING after character oracle string functions Character Manipulation INSTR and instr together in oracle instr in oracle examples combination of SUBSTR and instr in sql instr example oracle instr last occurrence how to use substr and INSTR together in oracle instr in sql w3schools oracle sql INSTRING after character
Views: 397 Oracle PL/SQL World
Oracle SQL Tutorial 11 - CREATE TABLE
 
05:04
The way you create a table is to use the CREATE TABLE command. CREATE TABLE users() So in this situation, the name comes right after the TABLE keyword. The next thing we do is put all of the columns on a line that we want to put in our table. CREATE TABLE users( user_id, username, first_name, last_name ) Notice the naming conventions here. For this series we are going to make columns with what is known as snake casing. This is where each individual word is separated by an underscore. if you have more than one column, all of them have to have commas except the last one. The comma is a way to say that another column is coming, so you don't need to do it on the last one. Now you would think we were done, but we also have to say what data type each column is. Later we will extensively discuss data types so we can focus on them exclusively. For now, here are the data types we are going to use: CREATE TABLE users( user_id NUMBER, username VARCHAR2(50), first_name VARCHAR2(50), last_name VARCHAR2(50) ) Now, inside of the parenthesis for varchar2, we pass in a number... This is the max length of the string. But the question is, what is it measured in? The default is actually in bytes, not characters. For example if we have the string hello, it is 5 characters, but it might take up a total of 10 bytes of storage. So I would recommend adding the keyword char right after the number so it defaults to 50 characters, not bytes. CREATE TABLE users( user_id NUMBER, username VARCHAR(50 CHAR), first_name VARCHAR2(50 CHAR), last_name VARCHAR2(50 CHAR) ) This will work to create a table, but it's really missing a lot of information… which column is the primary key? Are we adding any indexes? Is there any thing else we need to say about these columns? So as you can tell, we are making progress, but there is still so much to learn. The biggest gotcha to remember from this video is that the data type VARCHAR ends in a 2, stupid, right? who would end the name of something with a 2? Once again, this is Caleb from CalebTheVideoMaker2, and we will catch you in the next one! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 36933 Caleb Curry
Advanced unix String handling substr extraction find length of string array
 
07:46
Oracle SQL PLSQL and Unix Shell Scripting
Views: 556 Sridhar Raghavan
Learn MySQL In Arabic #23 - String Functions - Length, Char_Length
 
06:24
Learn how to get string length and char length
Views: 5754 Elzero Web School
Tutorial 10 : SQL LENGTH() Function - Part 1
 
02:45
Hi Friends! Here we are learning about LENGTH() function in Oracle Database. Let me know for any confusion for any doubt. Thanks!
Views: 50 YourSmartCode
PLSQL Class13 StringFunctions INITCAP LENGTH CONCAT REVERSE
 
09:50
Course : PL SQL Topic : StringFunctions INITCAP LENGTH CONCAT REVERSE (10 m) Here i discussed String Functions Watch till end and add comment about the video like it ! share it !
Oracle SQL Tutorial 26 - UTF-8 and UTF-16
 
05:46
UTF-8 and UTF-16 are different encodings for the Unicode character set. Let's discuss UTF-8 first. UTF-8 is what is known as a variable-length character set. This means that the amount of storage a character takes up depends on what character it is. For example, if we store the character A, it will only take up one byte. In fact, ASCII is a subset of UTF-8. That means UTF-8 encoding can work with ASCII data. If you are new to computer storage, a byte is a very small amount of information. The smallest thing a computer can store is a bit. 1 or 0. On or off. There are 8 bits in a byte, 1024 bytes in a kilobyte, 1024 kilobytes in a megabyte, 1024 megabytes in a gigabyte, and 1024 gigabytes in a terabyte, and 1024 terabytes in a petabyte. Considering it is completely possible for a database to be multiple petabytes, you can understand that a byte is very small. If you store a non-English character, the size of UTF-8 will increase to 2, 3, or 4 bytes. If you think back to when we used the VARCHAR data type, we passed in 50 CHAR. The reason we throw in that CHAR is that the default for Oracle is 50 characters. Now you can understand why adding the CHAR might be important. If a character can take up multiple bytes, you cannot guarantee 50 characters. Now, on to UTF-16. UTF-16 is also a variable length encoding, but it differs in that It is either 2 or 4 bytes. That means to store an A, it now takes two bytes rather than one. Even though a byte is so small, when you are storing billions of characters, an unnecessary byte really adds up to a lot of wasted storage. We can only represent so many characters with 2 bytes. When we run out of options, we move to four bytes to allow for other characters. Which do we use? It often depends on what platform you are on and also what languages you are working with. For example, if you are working with Asian language, UTF-16 stores each character in 2 bytes while UTF-8 stores each character in 3 bytes. So you could save space by using UTF-16. Additionally, UTF-16 works better when you are writing code in Java or something from Microsoft .NET because UTF-16, or a subset of it called UCS-2, is widely adopted. Other than that, UTF-8 will be the one you want. Now that we have built a pretty good foundation of character sets, we can now continue our discussion of data types. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 8406 Caleb Curry
Enough java.lang.String to Hang Ourselves...
 
46:25
Heinz Kabutz, CIO / CTO, JavaSpecialists.EU Dmytro Vyazelenko, Senior Software Engineer, Canoo Engineering AG Is it better to write "" + 42 or Integer.toString(42)? How much memory can you save if you intern() all your strings? How can String Deduplication reduce your memory footprint, and what does it cost? And how much memory will Java 9 strings save in Bulgaria? What is the maximum length of a constant string? And a dynamic string? How much faster is the new StringBuilder().append("Hello ").append(name).toString() than "Hello " + name? What are intrinsics, and how do they relate to strings? Are strings really immutable? How can you efficiently create substrings? You’ll get answers to these and many more questions in this session.
Views: 848 Oracle Developers
SQL: TRIM function
 
06:35
In this tutorial, you'll learn how to use trim function PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database. PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces. PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard. The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3] PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables. PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7] The StepSqlite product is a PL/SQL compiler for the popular small database SQLite. PL/SQL Program Unit A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database. The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label. Package Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are: Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Views: 3014 radhikaravikumar
88. MID and LENGTH Function in SQL (Hindi)
 
05:21
Please Subscribe Channel Like, Share and Comment Visit : www.geekyshows.com
Views: 6059 Geeky Shows
Oracle SQL Tutorial 32 - VARCHAR2 and NVARCHAR2
 
03:06
This video we are going to discuss the VARCHAR2 and the NVARCHAR2 data types. The previous videos are a good foundation to this video. I've actually discussed so much stuff in those videos that I don’t have a whole lot to say. Good for you, right? I discussed over the previous videos that you should prefer to use VARCHAR2 over CHAR. That's because there is not a difference in performance or storage for a VARCHAR2 column. The only difference is that an CHAR column forces each value to take up a certain length even if it's not. There is one difference between the variable length and fixed length data types here that you need to know about, and that is storage limits. CHAR has a limit of 2000 bytes, while VARCHAR2 has a limit of 4000 bytes. That means you can store twice as much junk in a VARCHAR2 column! Other than that, these data types work exactly the same. I recommend you always use the VARCHAR2 data types instead of the CHAR data types, and only use NVARCHAR2 if you have a non-Unicode database. This will allow you to store Unicode characters in a column. Now, the amount of storage you can put in a VARCHAR2 column is twice what you can put in a CHAR column, but 4000 characters is still not very many characters. This is where the LOB data types come in, which we will discuss in the next video! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 5787 Caleb Curry
ORACLE:  How to ADD leading zeros to string ?
 
00:43
Available for any version of Oracle Database.
Views: 63 1Click2beDBA
Oracle Tutorial - Case Manipulation Functions | UPPER | LOWER | INITCAP
 
12:00
Oracle Tutorials - Case Manipulation Functions UPPER | LOWER | INITCAP
Views: 100 Tech Acad
Nesting Functions Within Functions (Introduction to Oracle SQL)
 
03:47
Learn how to nest one function inside another function, and why this might be needed. The full Introduction to Oracle SQL course is available here: https://www.databasestar.com/introduction-to-oracle-sql-course/
Views: 323 Database Star
How to : Use formatting in oracle sql
 
05:09
Perception is key! Once you retrieve your data, you should think about how you would like to view it. You can do a lot of formatting in Oracle SQL, which translates into saved time for you. After all who wants dirty data? Formatting is a presentation issue. It allows you to present the data you have gathered so that it looks good. And as someone once said, "Perception is key." I use the following 8 formatting tricks. There are many more, but the ones below are essential to know. Round and/or truncate the date field to the nearest month: SELECT SYSDATE, ROUND(SYSDATE, ‘Wed'), TRUNC(SYSDATE, ‘Wed') FROM u.agg_ia_clickstream SYSDATE ROUND(SYSDA TRUNC(SYDATE -------------- ------------------------------------------------ 31-Dec-2003 01-Jan-2004 01-Dec-2003 Rounding allows the date to be rounded up to the nearest month. Truncation allows the date of any value you specify to its minimum. The minimum value is 1, so 31-Dec was truncated to 1-Dec. The REPLACE function can be used to do a search and replace: REPLACE(string, search, replace) Extract data from a string using: SUBS(STRING, START[, length]) Find the length of a string: LENGTH(string) Add strings together: String1 ll String2 Trim unwanted spaces, numbers, and letters: Trim(string) Trim(character FROM string) To trim only characters to the right: RTRIM(string) To trim characters only to the left: LTRIM(string) To remove punctuation from both sides of a string: RTRIM(LTRIM(string,'.;!'),'.,!') Change capitalization of letters: To Change letters to upper case letters: UPPER(string) To change letters to lower case letters: LOWER(string) To use upper case for the first letter and lower case for other letters: INITCAP(string) Search and Replace: SELECT date, unique cookies DECODE (dma, 360, ‘Dayton', 560, ‘Toledo', 870, ‘Baton Rouge', Null, ‘Unknown') FROM u.agg_ia_clickstream In this example, 360 is replaced by Dayton, 560 is replaced by Toledo, etc. The 8 formatting tips above will help you make your data -- and hopefully you, by association -- look good. If you know other tips that are helpful, please let me know. Thank you for reading. Required Tools: Oracle SQL Deductive Skills
Difference between Replace and Translate function in oracle
 
03:46
This video demonstrates the difference between a replace function and a translate function in oracle using an suitable example. Both the function does a similar task, but has a lot of difference in implementation and output when used. Be learning the difference users can easy justify the correct scenarios on where to use translate and when to use replace. If you want more such videos of exciting and amazing 'difference between' concepts, check out the links below : union and union all : https://youtu.be/n9FqQOd8liY char and varchar2 : https://youtu.be/039qzwjWf4k procedure and function : https://youtu.be/q3LmOenL120 in and exists : https://youtu.be/REX4IjRYlFw rank and dense_rank : https://youtu.be/WGSX998hZ9M delete and truncate : https://youtu.be/u76wMm2byXo %type and %rowtype : https://youtu.be/MlLUFeZ_3eM
Views: 2133 Kishan Mashru
Sql query to select all names that start with a given letter without like operator
 
03:02
Text Article http://csharp-video-tutorials.blogspot.com/2017/01/sql-query-to-select-all-names-that.html Slides http://csharp-video-tutorials.blogspot.com/2017/01/sql-query-to-select-all-names-that_31.html SQL Server Interview Questions and Answers text articles & slides http://csharp-video-tutorials.blogspot.com/2014/05/sql-server-interview-questions-and.html SQL Server Interview Questions and Answers playlist https://www.youtube.com/playlist?list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists In this video we will discuss writing a SQL query to retrieve all student names that start with letter 'M' without using the LIKE operator. If the interviewer has not mentioned not to use LIKE operator, we would have written the query using the LIKE operator as shown below. SELECT * FROM Students WHERE Name LIKE 'M%' We can use any one of the following 3 SQL Server functions, to achieve exactly the same thing. CHARINDEX LEFT SUBSTRING The following 3 queries retrieve all student rows whose Name starts with letter 'M'. Notice none of the queries are using the LIKE operator. SELECT * FROM Students WHERE CHARINDEX('M',Name) = 1 SELECT * FROM Students WHERE LEFT(Name, 1) = 'M' SELECT * FROM Students WHERE SUBSTRING(Name, 1, 1) = 'M'
Views: 63642 kudvenkat
How to work with String Functions in SQL/CONCAT, LENGTH, SUBSTR/SQL Tutorial/Eng, Kor Sub
 
02:38
What if you want to check the string information in a database? Then use the string function! (Eng/Kor Subtitle) [SQLGate Homepage] https://www.sqlgate.com/?language=en [SQLGate Blog] https://blog.sqlgate.com/
Views: 32 SQLGate
SQL Server SUBSTRING Extract Length Specified Position String Queries Command DB
 
01:40
SQL Server SUBSTRING Extract Length Specified Position String Database Queries Command DB.
Views: 51 Saidah Faulia
Oracle LOWER Function
 
01:30
https://www.databasestar.com/oracle-upper-lower/ The Oracle LOWER function is used to convert a string to a lowercase value. It’s useful for comparing text or string values that may have mixed case, such as user input or data from different tables. The opposite of the LOWER function is the UPPER function (which converts to upper case). The syntax of LOWER is: LOWER(input_string) The input_string is the string value to convert to a lower case value. It can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return type is the same as the input type. You can use the LOWER function in a WHERE clause. However, unless you have a function-based index on the column, any indexes won’t be used. For example, if you have an index on first_name, a query that uses “WHERE LOWER(first_name)” won’t use this index. You’ll have to create an index on the LOWER(first_name) for this to be used. It’s not required, but it’s just something to keep in mind. For more information about the Oracle LOWER function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-upper-lower/
Views: 56 Database Star
Oracle Tutorial - Date Functions NEXT_DAY | LAST_DAY
 
07:13
Oracle Tutorials - Date Functions NEXT_DAY( ) | LAST_DAY( )
Views: 130 Tech Acad
LPAD and RPAD in Oracle SQL | Character Manipulation | Oracle Tutorial for Beginners
 
09:00
LPAD and RPAD in Oracle SQL | Character Manipulation | Oracle Tutorial for Beginners Oracle Database Tutorial for Beginners Oracle SQL Tutorial for Beginners Oracle SQL lpad and rpad in oracle with examples Oracle Tutorial for Beginners LAPD function in Oracle RPAD function in Oracle LPAD in Oracle RPAD in Oracle lpad and rpad in oracle with examples LPAD() in Oracle RPAD() in Oracle how to use lpad and rpad at a time lpad oracle example how to use both lpad and rpad in oracle lpad in sql w3schools lpad and rpad in oracle with examples lpad in sql server lpad in oracle lpad and rpad in Oracle SQL
Views: 272 Oracle PL/SQL World
LTRIM Function In Oracle | Character Manipulation | Oracle SQL Tutorial for Beginners
 
04:53
LTRIM Function In Oracle | Character Manipulation | Oracle Tutorial for Beginners oracle sql tutorial for beginners LTRIM() Function in Oracle SQL LTRIM() Function in Oracle LTRIM() Function in SQL Oracle LTRIM Function ltrim and rtrim in oracle ltrim sql oracle trim function oracle string functions sql ltrim characters from string SQL TRIM Functions LTRIM Function in SQL LTRIM Function in Oracle RTRIM Function in SQL RTRIM FUnction in Oracle oracle trim multiple characters ltrim function in oracle oracle substring function How to remove space in Oracle String LTRIM and RTRIM in SQL LTRIM and RTRIM in Oracle
Views: 153 Oracle PL/SQL World