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.