Home
Search results “Oracle if instr”
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: 5334 Database Star
ascii,instr | sql functions | oracle database 11g version 2 |
 
07:21
executed in oracle database 11g version 2
Views: 344 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: 83447 Manish Sharma
Oracle Database11g tutorials 11: SQL case manipulation function in Oracle Database
 
04:38
Part 2 SQL functions- character function -case manipulation functions Previous Video Link :: http://youtu.be/5rx8Q4x4-qI 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 discuss in last video we have 3 case manipulation functions. In case you miss the SQL functions intro video you can find the link in the description below. First let's see what these functions are. Upper (): This function takes a parameter converts it into uppercase and returns the new string. Initcap (): this function also takes parameter and converts the initial letter of or parameter into uppercase and returns the new string. Lower (): This function takes a parameter converts it into lowercase and returns the new string. Ok let's do some practical For this practical we will be using 2 tables First is dual table Which is a dummy table provided by oracle? And I have created another table again by the name of example. This will be our second table. Let's start with SQL function upper ( ); SELECT upper('hello world') FROM dual; As you can see I have used "hello world" completely in lowercase as parameter to SQL upper function. Execute it. Here, our result has given us a completely new hello world string which is in uppercase. Similarly you can also use SQL upper function to perform other DML Let's try This time we will work on our second table EXAMPLE; insert into example values (upper('manish')); first parentheses for values and second for SQL function upper. Let's check the result. As you can see here inserted value is in upper case. Lets compare it with simple insert query Insert into example values ('manish'); Execute it, i think now it's clear that when we perform simple insert DML the inserted value will be the same you provide. Similarly let's try SQL function initcap(); SELECT initcap('hello world') FROM dual; As you see here in result the initial letters of string hello world are in uppercase. Let's do the insert query insert into example values (initcap('hello world')); initial letters of string also are in uppercase. Let's try SQL function lower(); This time we have to write our hello world in all caps. SELECT lower('HELLO WORLD') FROM dual; Execute String totally in lower case. Similarly you can user SQL function lower in other DML like insert and all.
Views: 89510 Manish Sharma
Oracle ADD_MONTHS Function
 
03:15
https://www.databasestar.com/oracle-add_months/ The Oracle ADD_MONTHS allows you to add a specified number of months to a date value, and return the new date value. It’s great for performing calculations on dates, or date arithmetic, which can be hard in many different programming languages. The syntax of the ADD_MONTHS function is: ADD_MONTHS ( input_date, number_months ) The function returns a DATE value. The parameters of this function are: - input_date (mandatory): This is the starting date, or the date that you will add a number of months to. - number_months (mandatory): This is an integer value that represents the number of months to add to input_date. The input_date can be any datetime value, or even a character value that can be converted to a date. Also, the number_months can be any number value, or a character value that can be converted to a number. What if the number of days in the two months are different? For example, adding a month to Jan 31 could result in Feb 31, which does not exist. Well, in this case, Oracle would work out that the date does not exist, and use the last date of the month (e.g. Feb 28). You can also provide a negative value for ADD_MONTHS to subtract months from the specified date. For more information about the Oracle ADD_MONTHS function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-add_months/
Views: 532 Database Star
Oracle CAST Function
 
03:23
https://www.databasestar.com/oracle-cast/ The Oracle CAST function allows you to convert one data type to another. It’s similar to the TO_NUMBER, TO_DATE, and TO_CHAR functions, but allows for more functionality, including converting to many other data types. The syntax of the CAST function is: CAST( {expr | MULTISET (subquery) } AS type_name ) The structure of this function is different to other functions, because it uses the word AS to separate parameters rather than a comma. You can run this function like many other functions, on a single row, which would look like this: CAST( expr AS type_name ) Or, you can run it as though it’s a subquery, using the MULTISET feature: CAST( MULTISET (subquery) AS type_name ) The parameters of the CAST function are: - expr (mandatory choice): This is the value or expression that is to be converted to another data type. - MULTISET (optional): This keyword indicates that the subquery results will return multiple rows, and are then CAST into a collection value. If this is omitted, and the subquery returns multiple rows, it will only return a single row. - subquery (mandatory choice): This is the subquery that can be run to be converted into a collection data type. - type_name (mandatory): This is the Oracle data type that the expr or subquery will be converted to. There are several data types that are compatible with the CAST function, both for converting from and to. You can find the full list in the article mentioned below. For more information about the Oracle CAST function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-cast/
Views: 2458 Database Star
NULL-Related Functions in Oracle
 
02:52
An overview of some of the functions Oracle provides to handle NULL values in SQL and PL/SQL. For more information see: https://oracle-base.com/articles/misc/null-related-functions Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Bjoern Rost Blog: http://portrix-systems.de/blog/brost/ Twitter: https://twitter.com/brost Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 2790 ORACLE-BASE.com
String Operations in ORACLE : Substr and Instr
 
44:32
Topic : String Operations in ORACLE : Substr and Instr 22th Video of this Series Comments/Suggestions are welcome get in touch with us, by mailing [email protected] for Upcoming trainings. This is an Unedited Video from the Live sessions on Sql (RDBMS) I will be posting a complete series of these tutorials on this channel, If you would like to be notified every time i post something ; Please subscribe to this channel and press the bell icon. Other Videos of same series : https://youtu.be/oxmOJW1S09Y https://youtu.be/lHKr9HNwCBA https://www.youtube.com/watch?v=b19w2vs8IE0 https://youtu.be/b19w2vs8IE0 https://youtu.be/Mtp81d-YBpI https://youtu.be/-t2unhp_T8g
Views: 35 CodeSmith 001
Oracle MONTHS_BETWEEN Function
 
02:17
https://www.databasestar.com/oracle-months_between/ The Oracle MONTHS_BETWEEN function allows you to find the number of months between two dates. You can enter two dates and the function returns the difference. This function is often used as part of other calculations. Sometimes you’ll want to know the difference between two dates to perform other functions. The syntax of the MONTHS_BETWEEN function is: MONTHS_BETWEEN(date1, date2) What do these two parameters mean? The function performs date1 minus date2. So, if date1 is larger, the result is positive. If date2 is larger, then the result is negative. What data type is returned? It depends on the values you provide. If the dates are both on the same day of the month (e.g. both on the 10th of the month), then the result is an integer or whole number. If not, then the result will be a decimal value. For more information on the MONTHS_BETWEEN function, including the code used in this video and examples, read the related article here: https://www.databasestar.com/oracle-months_between/
Views: 1142 Database Star
Oracle RPAD Function
 
04:22
https://www.databasestar.com/oracle-lpad/ The Oracle RPAD function is used to add extra characters to the right of a text value. This is called “padding”, and the function is called RPAD because the R stands for “right” and it “right pads” a text value. It’s the opposite of LPAD, which pads characters to the left of the value. The RPAD function can be useful for ensuring all values are the same length, or if there is another requirement you have for adding characters to the end. The syntax is: RPAD(expr, length [,pad_expression]) The expr parameter is the text value you want to pad or add characters to. The length is the total length the expression or value will be after the padding has been done. It’s not the number of characters to add. The pad_expression is an optional field and is the character or characters to add to the right end of the string. The default value is a space. If the length specified in the function is shorter than the length of the string, then the string is truncated to meet the length. For more information about the Oracle RPAD (and LPAD) function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-lpad/
Views: 232 Database Star
Oracle GREATEST Function
 
02:43
https://www.databasestar.com/oracle-greatest/ The Oracle GREATEST function is a useful function, but I don’t think it’s very well understood. It returns the greatest or largest value in a set of values. You can provide the values, and use numbers or text values. The syntax for the Oracle GREATEST function is: GREATEST(expr1 [, expr_n]) The expr1 is the first expression to use for your comparison. Expr_n is one or more expressions to use in the comparison, separated by commas. This is optional. So how is GREATEST calculated? If the parameters are numeric, the GREATEST function finds the largest number. If the parameters are characters, the function finds the latest value if they were sorted alphabetically (using their character values). What data type is returned? It depends on the parameters you provide. It could be the same as the parameters you provide, or VARCHAR2 if the parameters are all characters. You can also use the GREATEST function with dates. It’s also the opposite of the LEAST function. For more information on the GREATEST function, including the SQL used in this video and the examples, read the related post here: https://www.databasestar.com/oracle-greatest/
Views: 330 Database Star
Oracle LISTAGG Function
 
04:57
https://www.databasestar.com/oracle-listagg/ The Oracle LISTAGG function allows you to aggregate or combine string values from multiple rows into a single row. For example, it turns this data: FIRST_NAME Adam Brad Carrie Into this data: FIRST_NAME Adam, Brad, Carrie The same data is shown, but it’s in one row and not multiple. It’s a valuable string manipulation function, and once you know what it is and how it works, you’ll be able to use it in your queries as needed. So what does it look like? The function is: LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] These parameters are: measure_expr: This is a column or expression that you want to concatenate the values of. In the example above, it was the first_name column. Mandatory. delimiter: This is the character between each of the measure_expr values. Optional, the default is a comma. order_by_clause: This is the order that the values from the measure_expr are listed. Mandatory. query_partition_clause: This allows you to use LISTAGG as an analytic function, allowing you to show LISTAGG in groups for different rows. One thing to be aware of is that the output of the Oracle LISTAGG function is limited to 4,000 bytes. If you get more than this, you’ll receive an error. The most common way around this that I’ve seen is to write a custom function and use a CLOB. Watch the video to find out more and see some examples. For more information about the Oracle LISTAGG function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-listagg/
Views: 3354 Database Star
NULLIF FUNCTION IN ORACLE SQL WITH EXAMPLE
 
03:24
This video is the 3rd video in the series of 'working with null in oracle sql', the video demonstrates the use of NULLIF function in oracle sql, and how we can use it in day to day coding time. The video explains the function with perfect easy to understand example.
Views: 1534 Kishan Mashru
Oracle MOD Function
 
03:17
https://www.databasestar.com/oracle-remainder-mod/ The Oracle MOD function performs a “modulo division” on the numbers you specify. A modulo division is where a division is performed using two numbers, and the remainder is returned by the function. For example, the division of 8/5 is 1.6. Using modulo division, 8/5 will work out there is one 5 contained in the 8, with 3 left over. The MOD function would return 3 in this example. The syntax of the MOD function is: MOD(numerator, denominator) The numerator is the number mentioned first in the division, or the one that is on top of the division sign. In a division such as 15/4, this would be the 15. The denominator is the number mentioned second in the devision, or hte one that is on the bottom of the division sign. In a division such as 15/4, this would be the 4. The parameters can be any numeric data type, and the return type depends on these parameters. This means MOD can return a whole number or a decimal number. The calculation for the Oracle MOD function is: numerator - denominator * FLOOR(numerator / denominator). If the denominator is 0, the function returns the value of the numerator. This is done to prevent “divide by 0” errors. For more information about the MOD function, including how to return every second row, how it’s different to REMAINDER, and to see the SQL code used in these examples, read the related article here: https://www.databasestar.com/oracle-remainder-mod/
Views: 1185 Database Star
Oracle FROM_TZ Function
 
01:45
https://www.databasestar.com/oracle-timezone-functions/ The Oracle FROM_TZ function is used to convert a value in a TIMESTAMP data type, and a specific TIME ZONE, to a TIMESTAMP WITH TIME ZONE value. It’s a helpful conversion function if you work with times and time zones a lot. The syntax of the FROM_TZ function is: FROM_TZ ( timestamp_value, timezone_value ) The parameters of this function are: - timestamp_value: the value in a TIMESTAMP format to convert. - timezone_value: this is the timezone value that the timestamp_value will be converted in to. If you want to know what values can be used as a timezone value, you can look in the database view here: SELECT * FROM v$timezone_names; For more information about the Oracle FROM_TZ function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-timezone-functions/
Views: 98 Database Star
LNNVL FUNCTION IN ORACLE SQL
 
09:11
This video is the 5th video in the series of 'working with null', this video explains the working of the lnnvl function in oracle sql with simple and easy to understand example. LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS [NOT] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.
Views: 685 Kishan Mashru
Oracle REPLACE Function
 
03:55
https://www.databasestar.com/oracle-replace/ The Oracle REPLACE function is a string manipulation function that allows you to replace one text string with another. It can be a whole word or sentence, or even a single value. The syntax of the REPLACE function is: REPLACE ( whole_string, string_to_replace, [replacement_string]) To use this function, the parameters are: - whole_string (mandatory): This is the string that will be checked for the characters to be replaced. - string_to_replace (mandatory): This is the string that will be searched for within whole_string. - replacement_string (optional): This is the string that will be used to replace occurrences of string_to_replace. Because the last parameter is optional, if it is not specified, then the Oracle REPLACE function removes all occurrences of string_to_replace. You can replace carriage returns with REPLACE, but you’ll need to use the CHR function. You can replace NULL values in Oracle, but this is best done with another function such as NVL, rather than REPLACE. If you want to replace multiple characters, you can do this using nested REPLACE functions or a REGEXP_REPLACE function. For more information about the Oracle REPLACE function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-replace/
Views: 1318 Database Star
Oracle LENGTH Function
 
01:23
https://www.databasestar.com/oracle-length/ The Oracle LENGTH function allows you to find the length of a string, also known as the number of characters in a string. It’s a common feature in different languages, and SQL is no different. The LENGTH function only has one parameter: LENGTH ( string_value ) The parameter is the string_value which is the value to check the length of. The function returns a number that represents the number of characters in the function. Some points to remember about the Oracle LENGTH function: If string_value is NULL, then LENGTH will return NULL. If string_value is an empty string, the LENGTH will return NULL. The string_value can be any of the character data types - CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB. If the string_value is a CHAR data type, then the LENGTH will include any trailing spaces in the value. For more information about the Oracle LENGTH function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-length/
Views: 383 Database Star
Oracle CONCAT Function
 
02:40
https://www.databasestar.com/oracle-concat/ The Oracle CONCAT function allows you to concatenate, or combine, string values together into a single string value. It’s a standard string manipulation technique in many languages, and SQL is no different. The syntax of this function is: CONCAT( string1, string2 ) The function returns both string values joined together, without a space or any other characters in between. The return value is the same as the first string parameter, which can be any of the string data types. One of the main drawbacks of this function is that it only allows for two strings to be combined, unlike other CONCAT functions in other languages. If you want to combine more than two strings together, you’ll need to have nested CONCAT functions, or use the double pipe character || which also concatenates strings. However, the advantage of CONCAT is that it allows for concatenation when using different character sets that don’t support the double pipe method. CONCAT and the double pipe method have similar performance, so that’s not an issue. For more information about the Oracle CONCAT function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-concat/
Views: 821 Database Star
Oracle TO_CHAR Function
 
04:13
https://www.databasestar.com/oracle-to_char/ The Oracle TO_CHAR function is a common and useful string manipulation function. It allows you to convert a number or a date value into a string value. It allows you to take a number or a string, and convert it to a VARCHAR2 data type. The syntax of the function is: TO_CHAR( input_value, [format_mask], [nls_parameter] ) The parameters are: - input_value: this is the value to convert to a VARCHAR2 value. It can be one of many different number or date data types. - format_mask is an optional parameter and allows you to specify the format to display the output as. If this is omitted, the function uses a default format. - nls_parameter: this is also optional and lets you determine a regional parameter for displaying the output value. The format mask helps you determine what your output is displayed as. For example, if you provide the date of May 3rd, and the output is ‘03/05/2018’, how do you know if this is May 3rd or March 5th? Using a format mask can specify which format is used. The function is similar to the TO_NUMBER and TO_DATE functions, in that they both convert a specific data type. For more information about the Oracle TO_CHAR function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-to_char/
Views: 1543 Database Star
PL/SQL Tutorial 4 (Oracle): Substring Command
 
04:42
PL/SQL Tutorial 4 (Oracle): Substring Command In this tutorial I show you how to use the Substring command in PL/SQL Source Code: https://github.com/mitchtabian/SQL-tutorials Subscribe to my Blog and YouTube channel to get notifications when new FREE coding tutorials are posted! Blog: http://www.codingwithmitch.com/
Views: 216 CodingWithMitch
Oracle DECODE Function
 
04:31
https://www.databasestar.com/oracle-decode-function/ The Oracle DECODE function lets you perform IF-THEN-ELSE functionality in your queries. It’s similar to a CASE statement. The syntax of the DECODE function is: DECODE ( expression, search, result [, search, result]... [,default] ) The parameters of the Oracle DECODE function are: - expression (mandatory): This is the value to compare. - search (mandatory): This is the value to compare against the expression. - result (mandatory): This is the return value if the search value matches the expression value. There can be multiple combinations of search and result values, and the result value is attached to the previous search value. - default (optional): If none of the search values match, then this value is returned. If this is not provided, the DECODE function will return NULL if no matches are found. If you compare this to an IF-THEN-ELSE statement, it would look like this: IF (expression = search) THEN result [ELSE IF (expression = search) THEN result] ELSE default END IF The parameters can be one of many different data types, and the return type is calculated from them. Earlier I mentioned it’s similar to CASE. So why would you use one and not the other? - DECODE is an older function, and CASE was introduced as a replacement for DECODE. - CASE offers more flexibility then DECODE - CASE is also easier to read and debug (in my opinion) The performance of these functions is the same, so if you’re considering using DECODE, I would suggest using CASE instead. For more information about the Oracle DECODE function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-decode-function/
Views: 989 Database Star
Oracle SQL Tutorial 27 - CHAR Part 1
 
03:52
This video and the next is going to cover CHAR and NCHAR. Be sure to check out the previous two videos as these are going to introduce you to some foundational knowledge required to understand these data types. CHAR is a fixed-length data type. What that means is that every value for a CHAR column is going to be the same length. You specify the length in parenthesis when you create the table. The thing you need to know though is that the default measurement is in bytes. That means if you specify the length to be CHAR(50), the length of each value will be 50 bytes, by default. If you want to change that to 50 characters, you can pass in the word CHAR as in CHAR(50 CHAR). This is known as a qualifier. Specifically, they are known as length semantics qualifiers (describes the meaning of the given length). Now, I said the default was bytes, but you can actually change the default to characters. In that situation, you can actually use the keyword BYTE to break away from the default. In general, it's best to put CHAR or BYTE even if it is the default. In general, it's best to keep things consistent. It's okay to have these measured in CHAR or BYTE, but it is recommended that every column is the same. It allows you to be more consistent as if some columns measure length in bytes and some measure length in characters, things can get confusing. If you do want to change the default, look up NLS_LENGTH_SEMANTICS as well as the potential problems it may bring. What values are allowed in parenthesis? That is what we are going to 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: 5195 Caleb Curry
Oracle LPAD Function
 
04:27
https://www.databasestar.com/oracle-lpad/ The Oracle LPAD function is used to add extra characters to the left of a text value. This is called “padding”, and the function is called LPAD because the L stands for “left” and it “left pads” a text value. It’s the opposite of RPAD, which pads characters to the right of the value. The LPAD function can be useful for ensuring all values are the same length, or if there is another requirement you have for adding characters to the end. The syntax is: LPAD(expr, length [,pad_expression]) The expr parameter is the text value you want to pad or add characters to. The length is the total length the expression or value will be after the padding has been done. It’s not the number of characters to add. The pad_expression is an optional field and is the character or characters to add to the left end of the string. The default value is a space. If the length specified in the function is shorter than the length of the string, then the string is truncated to meet the length. For more information about the Oracle LPAD (and LPAD) function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-lpad/
Views: 754 Database Star
Oracle Database11g tutorials 10 ||  SQL Single Row Function (SQL Functions )
 
01:29
Single row functions 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 Email [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 The Code makers ============================================ in this oracle database tutorial we will see what are SQL FUNCTIONS. SQL functions are divided into two categories 1st is Single Row Functions And another one is Multiple row function which is also known as aggregate functions. But in today's SQL tutorial we will concentrate on Single Row Functions. Single-row functions operate on one row at a time and return one row of output for each input row. Single row functions are capable of manipulating data items and modifying the data types. Single row functions return one result per row and act on each row that is returned. Single row functions can accept one or more arguments and return one value. These arguments can be a column or an expression. With oracle we have 5 types of Single Row Functions 1. Character Functions 2. Number Functions 3. General Functions 4. Conversion Functions and 5. Date Functions. Character Functions are further divided into two categories. 1st is Case- manipulation functions and 2nd is Character Manipulation functions. We have 3 case manipulation functions Lower, upper and initcap and 7 character manipulation functions Sql functions concat, substr, length, instr, lpad, rpad, trim and replace. In our future video we will see what these functions are and what they do along with other SQL function. That's it for today's sql tutorial. Hope you enjoyed watching. Please don't forget to subscribe and like. Have a good day. This is Manish.
Views: 97221 Manish Sharma
Decode function in oracle plsql.
 
16:28
This oracle tutorial explain how to use decode function in oracle pl sql. Decode function is a powerful function which let you create a set of branched if else like statement within a single sql statement. I have also provided an example which swap values of 2 columns with a single sql statement using decode.
Views: 1765 Subhroneel Ganguly
28. SQL for Beginners - REGEXP_REPLACE( ) (Hindi) | Regular Expressions
 
08:30
PL/SQL Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY1I1I8ACgWYjI5WyFlV4Nin SQL Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY2q8cobbotVbn36XOskQR2F JavaScript Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY2tLfvp7bCL3c-psMH9Czs6 HTML5 Playlist: https://www.youtube.com/playlist?list=PLysly0KYnAY2_-PqGSrVTgBpVshxpqQlw Mobile Responsive Form using HTML5 & CSS: https://youtu.be/SECTfL3R0B0 MS SQL Server Installation: https://youtu.be/rZRA692RmuU Clock Animation in PowerPoint : https://youtu.be/LM5iwzNqQQo Envelope Animation in PowerPoint : https://youtu.be/HWlqGGleQqs FACEBOOK PAGE https://www.facebook.com/ishwaracademy/
Views: 283 Ishwar Academy
Oracle SQL Video Tutorial 26 - CASE Statements
 
05:59
Oracle SQL Video Tutorials 26 - CASE Statements explained.In This Oracle SQL Tutorial for Beginners, I have explained basics of SQL.These examples work on Oracle 10g and Oracle 11g database. These helps youto understand Oracle Joins and helps beginners. Iam going to add more tutorials on Oracle DBA, Oracle RAC and Oracle PL/SQL.All Oracle sql tutorial with examples are executed on Oracle 11g using Oracle SQL Developer.
Views: 5378 Just Channel
SQL tutorial 51: DECODE function in Oracle Database By Manish Sharma (RebellionRider)
 
05:04
SQL Tutorial 51 Decode function in Oracle Database. ● What Is DECODE() ● DECODE ( ) vs CASE ● Syntax of DECODE() ● Query 1. String Matching using DECODE() ● Query 2. DECODE() comparing values form the COLUMN of the table Celebrating 1000 subscribers. Thanks a lot guys for all your love and support. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: ------------------------------------------------------------------------- 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 first 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 [email protected] 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: 36452 Manish Sharma
Oracle COALESCE Function
 
04:00
https://www.databasestar.com/oracle-coalesce/ The COALESCE function in Oracle allows you to input one or many values and return a value that is not NULL. You provide it with a series of values, and it returns the first of thsoe values which are not NULL. The syntax of the Oracle COALESCE function is: COALESCE(expr1, expr2 [, expr_n]) These parameters are: Expr1: the first expression to check for a NULL value. If it’s NULL, the function checks the next parameter. If not, then this value is returned. Expr2: the second expression to check for a NULL value, if the first expression is NULL. If this is NULL, then the next expression is checked, otherwise this value is returned. Expr_n: More expressions can be added and checked in case both expr1 and expr2 are null. Only the first two expressions are needed. The first non-NULL expression is returned. But, if all expressions are NULL, then NULL is returned. One way I’ve recently used this was to find out what phone number to display. A query looked at the mobile number and returned that, but if it was NULL it looked at the home phone and returned that, but if that was NULL it looked to the business phone number. That query looked like this: COALESCE(mobile_phone, home_phone, business_phone) For more information about the Oracle COALESCE function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-coalesce/
Views: 640 Database Star
DECODE FUNCTION IN ORACLE SQL
 
09:36
This video tutorial explains with proper example on how to write a decode statement, this video also demonstrates how the decode statement behaves on null values and implicit datatype conversion that happens in a decode statement.
Views: 2955 Kishan Mashru
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: 438 Puzzle Guru
Oracle UPPER Function
 
02:32
https://www.databasestar.com/oracle-upper-lower/ The Oracle UPPER function is used to convert a string to an uppercase value. It capitalises a string 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 UPPER function is the LOWER function (which converts to lower case). The syntax of UPPER is: UPPER(input_string) The input_string is the string value to convert to an upper 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 UPPER 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 UPPER(first_name)” won’t use this index. You’ll have to create an index on the UPPER(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 UPPER 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: 179 Database Star
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: 2218 Kishan Mashru
What is the difference between decode and case
 
11:00
What is the difference between case and decode List of all the SQL and PLSQL interview questions @ https://easy-learning-tech.blogspot.com/p/oracle-sql-plsql-interview-questions.html
Views: 1974 Siva Academy
Learn INSTR Function - VBA Hindi -Series 10 - Video 691
 
13:47
Learn INSTR function in Excel VBA in hindi. Difference between INSTR and find function. .If you want to help this channel please bring more subscribers and if you like to do some donation please go to my website www.excelvbalover.com and click on "donation link" if you are new to channel please watch below playlist first 1.Excel VBA Introduction Hindi 2.VBA Variables Hindi 3.VBA Loops Hindi 4.VBA Collection loops Hindi 5. VBA Arrays
Views: 1700 Ajay Kumar
If() Function
 
06:57
This video reviews the if() function available in Oracle Service Cloud Analytics. You will learn how the if function() can increase the logic of reporting output. Also covered are subtle tips and tricks of business use case examples to help you think about how you might use this function in your reporting.
SQL: Union Vs UnionAll
 
04:37
In this tutorial, you'll learn what is union & unionall 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: 2243 radhikaravikumar
BluePrism - String Reg Expressions || Reality & Useful
 
19:58
Hi Friends, Now we will discuss about 'Utility - String' Regular expressions -------------------------------------------------------------------------- Why we need to use regular expressions and which scenario we need to use them? -------------------------------------------------------------------------- Let's take an example, I have one data item contains alphabates and I want to check wheather the data item having only alphabates or numbers or alphanumarics or any other condition checks. These type of operations we can do using regular expressions. -------------------------------------------------------------------------- we can find the syntaxs of regular expressions in w3scools. see here few of the regular expressions [abc] the string should contains a, b, c letters combination only [^abc] doesn't contains the combination of a, b, c or individually [0-9] string must contains only digits [^0-9] string should not contain numbers (x|y) Find any of the alternatives specified --------------------------------------------------------------------------- let's try on these expressions using 'Utility - String' regular expressions See we have an Action to use 'Test Regex Match' and it is expecting the inputs Regex Pattern - This is regular expression syntax Target String - This is, on which string we have to match Output will be the Flag type mean, either matching or not, true or false --------------------------------------------------------------------------- 1. Let's check with the number [0-9] pattern My target string should contain only numbers not any other symboles or alphabates. let's check it See I got true, because my target string having only number. If I give alphabates? see the result -- I got the false result What if I give combination of alphabates and numbers or other symboles too? see once Well, we got true. That means it is accepting the other characters along with the numbers. Let's restrict the alphabates in this using [0-9] ^[a-b] we got the false. --------------------------------------------------------- 2. Now we will see about [abc] expression I gave target string with in the abc letters that's why I got true. Let's include other characters. It gave me true and accepting the other letters If we don't want other letters to accept include ^[d-z] Let's try See now we got false. ^ means inverse/not case Now we will avoid the abc letters and use it for [abc] expression Results are false, because Target string doesn't contain abc letters. Make sure to know about the meaning of regular expression first. already we got idea about ^ symbol ^ - not case [] - are used for expression symbol - - used for range(between) like 0 to 9 or between 'a' and 'z' Thank you for watching video
Views: 9754 Reality & Useful
Oracle LEAST Function
 
02:46
https://www.databasestar.com/oracle-greatest/ The Oracle LEAST function is a useful function, but I don’t think it’s very well understood. It returns the least or smallest value in a set of values. You can provide the values, and use numbers or text values. The syntax for the Oracle LEAST function is: LEAST(expr1 [, expr_n]) The expr1 is the first expression to use for your comparison. Expr_n is one or more expressions to use in the comparison, separated by commas. This is optional. So how is LEAST calculated? If the parameters are numeric, the LEAST function finds the smallest number. If the parameters are characters, the function finds the earliest value if they were sorted alphabetically (using their character values). What data type is returned? It depends on the parameters you provide. It could be the same as the parameters you provide, or VARCHAR2 if the parameters are all characters. You can also use the LEAST function with dates. It’s the opposite of the GREATEST function. For more information on the LEAST function, including the SQL used in this video and the examples, read the related post here: https://www.databasestar.com/oracle-greatest/
Views: 182 Database Star
Mutating Trigger in Oracle - Mutating Table in Oracle - Mutating Table Error in Oracle Trigger
 
25:41
Mutating table error in oracle database comes only in Row Level trigger. Mutating trigger error in Oracle is not thrown by the Statement Level triggers. Mutating table error in oracle trigger is common when row level trigger contains an SQL statement targeted at the same table on which the trigger has been written. Become patron : https://www.patreon.com/crazy4db/ #Crazy4DB #OraclePLSQL #LearnPLSQL Important reference videos : Trigger predicates : https://youtu.be/bG9hPV_hzNU DML Triggers : https://youtu.be/D71vWBZMqIk Trigger basics : https://youtu.be/_XSaPlQRQBg PL/SQL Package : https://youtu.be/1_IVMS35oJM For integrity constraints : https://www.youtube.com/playlist?list=PLdNnjIwbbBtZgOBw2u-IjsaF8tVDJZcBZ For Views : https://youtu.be/K_NaE5q9yPU My web site : http://www.oracletrainingnagpur.com My blog : https://crazy4db.blogspot.in My facebook page : https://www.facebook.com/crazy4db/
Views: 88 Crazy4 DB
Oracle interview question, Difference between Rank and Dense Rank
 
03:56
Oracle interview question Difference between Rank and Dense Rank Both RANK() and DENSK_RANK() function gives the ranking within the ordered partition of data, However if same rank is assigned to more than one record, then the next rank(s) will be skipped by RANK() function, whereas, the ranks are consecutive for DENSE_RANK. No ranks will be skipped in case of DENSE_RANK even if more than one row are assigned with same rank. List of all the SQL and PLSQL interview questions https://easy-learning-tech.blogspot.com/p/oracle-sql-plsql-interview-questions.html
Views: 1084 Siva Academy
Learn Oracle | Using Case Function, Decode Function in SQL
 
03:31
Pebbles present, Learn Oracle 10g with Step By Step Video Tutorials. Learn Oracle 10g Tutorial series contains the following videos : Learn Oracle - History of Oracle Learn Oracle - What is Oracle - Why do we need Oracle Learn Oracle - What is a Database Learn Oracle - What is Grid Computing Learn Oracle - What is Normalization Learn Oracle - What is ORDBMS Learn Oracle - What is RDBMS Learn Oracle - Alias Names, Concatenation, Distinct Keyword Learn Oracle - Controlling and Managing User Access (Data Control Language) Learn Oracle - Introduction to SQL Learn Oracle - Oracle 10g New Data Types Learn Oracle - How to Alter a Table using SQL Learn Oracle - How to Create a Package in PL SQL Learn Oracle - How to Create a Report in SQL Plus Learn Oracle - How to Create a Table using SQL - Not Null, Unique Key, Primary Key Learn Oracle - How to Create a Table using SQL Learn Oracle - How to Create a Trigger in PL SQL Learn Oracle - How to Delete Data from a Table using SQL Learn Oracle - How to Drop and Truncate a Table using SQL Learn Oracle - How to Insert Data in a Table using SQL Learn Oracle - How to open ISQL Plus for the first time Learn Oracle - How to Open SQL Plus for the First Time Learn Oracle - How to Update a Table using SQL Learn Oracle - How to use Aggregate Functions in SQL Learn Oracle - How to use Functions in PL SQL Learn Oracle - How to use Group By, Having Clause in SQL Learn Oracle - How to Use Joins, Cross Join, Cartesian Product in SQL Learn Oracle - How to use Outer Joins (Left, Right, Full) in SQL Learn Oracle - How to use the Character Functions, Date Functions in SQL Learn Oracle - How to use the Merge Statement in SQL Learn Oracle - How to use the ORDER BY Clause with the Select Statement Learn Oracle - How to use the SELECT Statement Learn Oracle - How to use the Transactional Control Statements in SQL Learn Oracle - How to use PL SQL Learn Oracle - Data Types in PL SQL Learn Oracle - Exception Handling in PL SQL Learn Oracle - PL SQL Conditional Logics Learn Oracle - PL SQL Cursor Types - Explicit Cursor, Implicit Cursor Learn Oracle - PL SQL Loops Learn Oracle - Procedure Creation in PL SQL Learn Oracle - Select Statement with WHERE Cause Learn Oracle - SQL Operators and their Precedence Learn Oracle - Using Case Function, Decode Function in SQL Learn Oracle - Using Logical Operators in the WHERE Clause of the Select Statement Learn Oracle - Using Rollup Function, Cube Function Learn Oracle - Using Set Operators in SQL Learn Oracle - What are the Different SQL Data Types Learn Oracle - What are the different types of Databases Visit Pebbles Official Website - http://www.pebbles.in Subscribe to our Channel – https://www.youtube.com/channel/UCNNjWVsQqaMYccY044vtHJw?sub_confirmation=1 Engage with us on Facebook at https://www.facebook.com/PebblesChennai Please Like, Share, Comment & Subscribe
Views: 425 Pebbles Tutorials
Crystal Reports Tip of the Week - Using INSTR the instring function.wmv
 
17:22
Visit us at http://www.symphonycorp.com/
Views: 1446 SymphonyCorp
Aula 04 - Primeiros Passos no PL/SQL
 
10:27
Na quarta aula do nosso curso de Oracle você vai aprender os seus primeiros passos no PL/SQL e é muito importante que você tenha assistido a nossa terceira aula onde passamos todos os detalhes para que você possa fazer os seus primeiros comandos SQL no Oracle Database. https://www.youtube.com/watch?v=E3vu--LFzqw Para assistir ao vídeo completo basta acessar ao link abaixo https://www.youtube.com/watch?v=vOqatv42tkc&list=PLMaqczuL07LlmZva7iRS8DjyMYg2tptIk Nesta aula você vai aprender a criar o seu primeiro bloco anônimo no PL/SQL, declaração de variáveis e como fazer a sua primeira estrutura de decisão com o Oracle IF. Na próxima aula você vai aprender como transformar tudo isso em um programa dentro do Oracle criando a sua primeira procedure no Oracle. https://www.youtube.com/watch?v=QLpIPnwADeY
Views: 11958 Aprenda PL/SQL
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: 2238 radhikaravikumar
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: 62 Database Star