Friday 1 October 2021

Format function sql videos in telugu 28

 https://youtu.be/l55OWGuAplA

----------------------------------------------
INSERT Inserts a string within a string at the specified position and for a certain number of characters
INSERT(string, position, number, string2)


SELECT INSERT("vlr Training", 5, 3, "tech");
-----------


INSTR, Returns the position of the first occurrence of a string in another string

SELECT name, INSTR(name, "b")
FROM emp;
----------------------

LCASE Converts a string to lower-case

SELECT LCASE("VLR TRaining") ;
select lcase(name) from emp;
--------------
LEFT Extracts a number of characters from a string (starting from left)
SELECT LEFT("Vlr Training", 5) AS ExtractString;
--------------

LENGTH Returns the length of a string (in bytes)
SELECT LENGTH("vlr sql") AS LengthOfString;
--------------

LOCATE Returns the position of the first occurrence of a substring in a string

The LOCATE() function returns the position of the first occurrence of a substring in a string.

If the substring is not found within the original string, this function returns 0.

This function performs a case-insensitive search.

Note: This function is equal to the POSITION() function.

SELECT LOCATE("g", "vlrtraining.in") AS MatchPosition;
-----------------
| venkat  |                0 |
| praveen |                1 |
| mounika |                0 |
| revathi |                0 |
| anji    |                0 |
| harika  |                0 |
| praveen |                1 |
| pandu   |                1 |
| pandu   |                1 |
+---------+------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> SELECT name, INSTR(name, "k") from emp;
+---------+------------------+
| name    | INSTR(name, "k") |
+---------+------------------+
| venkat  |                4 |
| praveen |                0 |
| mounika |                6 |
| revathi |                0 |
| anji    |                0 |
| harika  |                5 |
| praveen |                0 |
| pandu   |                0 |
| pandu   |                0 |
+---------+------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> SELECT name, INSTR(name, "e") from emp;
+---------+------------------+
| name    | INSTR(name, "e") |
+---------+------------------+
| venkat  |                2 |
| praveen |                5 |
| mounika |                0 |
| revathi |                2 |
| anji    |                0 |
| harika  |                0 |
| praveen |                5 |
| pandu   |                0 |
| pandu   |                0 |
+---------+------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> SELECT name, INSTR(name, "e,a") from emp;
+---------+--------------------+
| name    | INSTR(name, "e,a") |
+---------+--------------------+
| venkat  |                  0 |
| praveen |                  0 |
| mounika |                  0 |
| revathi |                  0 |
| anji    |                  0 |
| harika  |                  0 |
| praveen |                  0 |
| pandu   |                  0 |
| pandu   |                  0 |
+---------+--------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> SELECT name, INSTR(name, "e") from emp;
+---------+------------------+
| name    | INSTR(name, "e") |
+---------+------------------+
| venkat  |                2 |
| praveen |                5 |
| mounika |                0 |
| revathi |                2 |
| anji    |                0 |
| harika  |                0 |
| praveen |                5 |
| pandu   |                0 |
| pandu   |                0 |
+---------+------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select insert("vlr training",5,3,"tech");
+-----------------------------------+
| insert("vlr training",5,3,"tech") |
+-----------------------------------+
| vlr techining                     |
+-----------------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select insert("vlr training",5,1,"tech");
+-----------------------------------+
| insert("vlr training",5,1,"tech") |
+-----------------------------------+
| vlr techraining                   |
+-----------------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select insert("vlr training",5,0,"tech");
+-----------------------------------+
| insert("vlr training",5,0,"tech") |
+-----------------------------------+
| vlr techtraining                  |
+-----------------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select insert("vlr training",1,0,"tech");
+-----------------------------------+
| insert("vlr training",1,0,"tech") |
+-----------------------------------+
| techvlr training                  |
+-----------------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select insert("vlr training",1,0," tech")
+------------------------------------+
| insert("vlr training",1,0," tech") |
+------------------------------------+
|  techvlr training                  |
+------------------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select insert("vlr training",1,3," tech")
+------------------------------------+
| insert("vlr training",1,3," tech") |
+------------------------------------+
|  tech training                     |
+------------------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select * from emp
    -> ;
+----+---------+-----------+------+-------+
| id | name    | loc       | age  | sal   |
+----+---------+-----------+------+-------+
| 10 | venkat  | kphb      |   35 | 30000 |
| 11 | praveen | pune      |   28 | 35000 |
| 12 | mounika | hyderabad |   22 | 39000 |
| 13 | revathi | jntu      |   26 | 20000 |
| 14 | anji    | gutta     |   28 | 20000 |
| 15 | harika  | lb nagar  |   26 | 30100 |
| 16 | praveen | jntu      |   33 |   100 |
| 17 | pandu   | munipeda  |   35 | 30300 |
| 18 | pandu   | pune      |   23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select insert(name,1,0,"vlr ") from emp;
+-------------------------+
| insert(name,1,0,"vlr ") |
+-------------------------+
| vlr venkat              |
| vlr praveen             |
| vlr mounika             |
| vlr revathi             |
| vlr anji                |
| vlr harika              |
| vlr praveen             |
| vlr pandu               |
| vlr pandu               |
+-------------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select name,insert(name,1,0,"vlr ") from
+---------+-------------------------+
| name    | insert(name,1,0,"vlr ") |
+---------+-------------------------+
| venkat  | vlr venkat              |
| praveen | vlr praveen             |
| mounika | vlr mounika             |
| revathi | vlr revathi             |
| anji    | vlr anji                |
| harika  | vlr harika              |
| praveen | vlr praveen             |
| pandu   | vlr pandu               |
| pandu   | vlr pandu               |
+---------+-------------------------+
9 rows in set (0.001 sec)

MariaDB [vlrinst]> select instr("vlr training","in");
+----------------------------+
| instr("vlr training","in") |
+----------------------------+
|                          8 |
+----------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select instr("vlr training","is");
+----------------------------+
| instr("vlr training","is") |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select name instr(name,"va") from emp;;
ERROR 1064 (42000): You have an error in your SQL syntax; ch
ERROR: No query specified

MariaDB [vlrinst]> select name, instr(name,"va") from emp;;
+---------+------------------+
| name    | instr(name,"va") |
+---------+------------------+
| venkat  |                0 |
| praveen |                0 |
| mounika |                0 |
| revathi |                3 |
| anji    |                0 |
| harika  |                0 |
| praveen |                0 |
| pandu   |                0 |
| pandu   |                0 |
+---------+------------------+
9 rows in set (0.001 sec)

ERROR: No query specified

MariaDB [vlrinst]> select name, instr(name,"ve") from emp;;
+---------+------------------+
| name    | instr(name,"ve") |
+---------+------------------+
| venkat  |                1 |
| praveen |                4 |
| mounika |                0 |
| revathi |                0 |
| anji    |                0 |
| harika  |                0 |
| praveen |                4 |
| pandu   |                0 |
| pandu   |                0 |
+---------+------------------+
9 rows in set (0.000 sec)

ERROR: No query specified

MariaDB [vlrinst]> select name, instr(name,"e") from emp;;
+---------+-----------------+
| name    | instr(name,"e") |
+---------+-----------------+
| venkat  |               2 |
| praveen |               5 |
| mounika |               0 |
| revathi |               2 |
| anji    |               0 |
| harika  |               0 |
| praveen |               5 |
| pandu   |               0 |
| pandu   |               0 |
+---------+-----------------+
9 rows in set (0.001 sec)

ERROR: No query specified

MariaDB [vlrinst]> select lcase("Vlr TRining") ;
+----------------------+
| lcase("Vlr TRining") |
+----------------------+
| vlr trining          |
+----------------------+
1 row in set (0.061 sec)

MariaDB [vlrinst]> select length("vlr");
+---------------+
| length("vlr") |
+---------------+
|             3 |
+---------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select length("vlr ");
+----------------+
| length("vlr ") |
+----------------+
|              4 |
+----------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select length(44);
+------------+
| length(44) |
+------------+
|          2 |
+------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select length(444);
+-------------+
| length(444) |
+-------------+
|           3 |
+-------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select locate("vlrtraining.in","i");
+------------------------------+
| locate("vlrtraining.in","i") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.032 sec)

MariaDB [vlrinst]> select locate("i","vlrtraining.in");
+------------------------------+
| locate("i","vlrtraining.in") |
+------------------------------+
|                            7 |
+------------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select locate("z","vlrtraining.in");
+------------------------------+
| locate("z","vlrtraining.in") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]>

No comments:

Post a Comment

Note: only a member of this blog may post a comment.