Главная » Языки программирования

String functions in php mysql

На чтение 8 мин
Содержание
  1. PHP Strings
  2. 3. Some PHP function for strings
  3. MySQL String Functions¶
  4. LEFT¶
  5. REPLACE¶
  6. SUBSTRING¶
  7. TRIM¶
  8. FIND_IN_SET¶
  9. FORMAT¶
  10. References¶
  11. Встроенные функции

PHP Strings

A string is simply a quoted chunk of characters: letters, numbers, spaces, punctuation, and so forth. Strings can be contained within either single or double quotation marks, and in a HEREDOC construction.
These are all strings: «Web Courses», ‘coursesweb.net’, «1976», ‘$5, 78%’, ‘August, 23, 2011’ .
To make a string variable, assign a string value to a valid variable name:

  • \n — new line
  • \t — tab
  • \r — carriage return
  • \\ — backslash
  • \$ — dollar sign
  • \» — double quote

— The «\n» character combination is seen by PHP as a new line directive when written within a double-quoted string (this does not work the same way with single quotes).

To print out the value of a string, use either echo or print .
To print the value of a variable within a context, use double quotation marks. If you add a variable inside simple quotation marks, will display its name (not its value).

• Another way to build a string is to use a construct called HEREDOC. It’s used to build longer strings, and therefore makes them more readable to the programmer.
Begin the HEREDOC with three less than ( Concatenation is used to join strings. It’s performed using the concatenation operator (.).

— You see that we used the concatenation operator (.) three times. The second (.) is used to insert a comma and a space character ( ‘, ‘ ), to separate the value of the two variables.
If you are concatenating one value to another, you can use the concatenation assignment operator ( .= ).

3. Some PHP function for strings

PHP has a lot of useful string-specific functions. Here are just some example, the complete list can be found at the official website: PHP string functions.

— Strip whitespace (or other characters, specified at ‘character’) from the beginning and end of a string. Without the second parameter, trim() will strip ordinary white space.

— var_dump() displays structured information about its parameter, that includes its type, the number of characters and value.

Читайте также:  No package sqlite3 found php

— Returns the length of a string.

— Counts the number of words inside «string».

— Returns a string with the first character of each word in «string» capitalized.

— Returns «string» with all alphabetic characters converted to lowercase.

— Return the portion of the string from the beginning of the ‘needle’ to the end of the «string». If the ‘needle’ is not found, False is returned. ‘needle’ and «string» are examined in a case-insensitive manner.

— Returns the numeric position of the first occurrence of ‘needle’ in the «string». If the ‘needle’ is not found, False is returned. ‘needle’ and «string» are examined in a case-insensitive manner.

— Removes embedded HTML tags from within «string».

html 
tags
'; echo strip_tags($str); // Example with html tags ?>

— Replace all occurrences of the ‘search’ string with the ‘replace’ string in «subject» (case-insensitive).

Источник

MySQL String Functions¶

Get the length of a string in bytes and in characters.

SHOW CHARACTER SET; SET @s = CONVERT('MySQL String Length' USING ucs2); SELECT CHAR_LENGTH(@s), LENGTH(@s); SET @s = CONVERT('MySQL string length' USING latin1); SELECT LENGTH(@s), CHAR_LENGTH(@s); SET @s = CONVERT('MySQL string length' USING utf8); SELECT LENGTH(@s), CHAR_LENGTH(@s); SET @s = CONVERT('á' USING utf8); SELECT CHAR_LENGTH(@s), LENGTH(@s); CREATE TABLE posts( postid int auto_increment primary key, title varchar(255) NOT NULL, excerpt varchar(255) NOT NULL, content text, pubdate datetime )Engine=InnoDB; INSERT INTO posts(title,excerpt,content) VALUES('MySQL Length','MySQL string length function tutorial','dummy'), ('Second blog post','Second blog post','dummy'); SELECT postid,title,IF(CHAR_LENGTH(excerpt) > 20,CONCAT(LEFT(excerpt,20), '. '),excerpt) summary FROM posts;

LEFT¶

Get the left part of a string with a specified length.

SELECT LEFT('MySQL LEFT', 5); SELECT LEFT('MySQL LEFT', 9999); SELECT LEFT('MySQL LEFT', 0); SELECT LEFT('MySQL LEFT', -2); SELECT LEFT('MySQL LEFT', NULL); SELECT productname, LEFT(productDescription, 50) summary FROM products; SELECT LEFT(productdescription, 50) FROM products; SELECT REVERSE(LEFT(productdescription, 50)) FROM products; SELECT LOCATE(' ',REVERSE(LEFT(productdescription, 50))) first_space_pos FROM products; SELECT IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(productDescription, 50))), 0) - 1, 0) FROM products; SELECT productDescription,(50 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(productDescription, 50))), 0) - 1, 0)) last_space_pos FROM products; SELECT productDescription, LEFT(productDescription, last_space_pos) FROM (SELECT productDescription,(50 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(productDescription, 50))), 0) - 1, 0)) last_space_pos FROM products) AS t;

REPLACE¶

Search and replace a substring in a string.

UPDATE products SET productDescription = REPLACE(productDescription,'abuot','about');

SUBSTRING¶

Extract a substring starting from a position with a specific length.

SELECT SUBSTRING('MYSQL SUBSTRING', 7); SELECT SUBSTRING('MySQL SUBSTRING',-10); SELECT SUBSTRING('MYSQL SUBSTRING', 0); SELECT SUBSTRING('MySQL SUBSTRING' FROM -10); SELECT SUBSTRING('MySQL SUBSTRING',1,5); SELECT SUBSTRING('MySQL SUBSTRING' FROM 1 FOR 5); SELECT SUBSTRING('MySQL SUBSTRING',-15,5); SELECT SUBSTRING('MySQL SUBSTRING' FROM -15 FOR 5);

TRIM¶

Remove unwanted characters from a string.

SELECT TRIM(' MySQL TRIM Function '); SELECT TRIM(LEADING FROM ' MySQL TRIM Function '); SELECT TRIM(TRAILING FROM ' MySQL TRIM Function '); UPDATE products SET productname = TRIM(productname); SELECT LTRIM(' MySQL LTRIM function'); SELECT RTRIM('MySQL RTRIM function ');

FIND_IN_SET¶

Find a string within a comma-separated list of strings.

SELECT FIND_IN_SET('y','x,y,z'); -- 2 SELECT FIND_IN_SET('a','x,y,z'); SELECT FIND_IN_SET('a',''); SELECT FIND_IN_SET(NULL,'x,y,z'); SELECT FIND_IN_SET('a',NULL); CREATE TABLE IF NOT EXISTS divisions ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(25) NOT NULL, belts VARCHAR(200) NOT NULL ); INSERT INTO divisions(name,belts) VALUES ('O-1','white,yellow,orange'), ('O-2','purple,green,blue'), ('O-3','brown,red,black'), ('O-4','white,yellow,orange'), ('O-5','purple,green,blue'), ('O-6','brown,red'), ('O-7','black'), ('O-8','white,yellow,orange'), ('O-9','purple,green,blue'), ('O-10','brown,red'); SELECT name, belts FROM divisions WHERE FIND_IN_SET('red', belts); SELECT name, belts FROM divisions WHERE NOT FIND_IN_SET('black', belts); SELECT name, belts FROM divisions WHERE name IN ('O-1' , 'O-2'); SELECT name, belts FROM divisions WHERE FIND_IN_SET(name, 'O-1,O-2');

FORMAT¶

Format a number with a specific locale, rounded to the number of decimals

SELECT FORMAT(12500.2015, 2); SELECT FORMAT(12500.2015, 0); SELECT FORMAT(12500.2015, 2,'de_DE'); SELECT productname, quantityInStock * buyPrice stock_value FROM products; SELECT productname,CONCAT('$',FORMAT(quantityInStock * buyPrice, 2)) stock_value FROM products; SELECT productname,CONCAT('$',FORMAT(quantityInStock * buyPrice, 2)) stock_value FROM products ORDER BY stock_value; SELECT productname,CONCAT('$',FORMAT(quantityInStock * buyPrice, 2)) stock_value FROM products ORDER BY quantityInStock * buyPrice;

References¶

Источник

Читайте также:  Currency php on line 141warning division by zero in

Встроенные функции

Для работы со строка в MySQL определен ряд встроенных функций:

    CONCAT : объединяет строки. В качестве параметра принимает от 2-х и более строк, которые надо соединить:

SELECT CONCAT('Tom', ' ', 'Smith') -- Tom Smith
SELECT CONCAT_WS(' ', 'Tom', 'Smith', 'Age:', 34) -- Tom Smith Age: 34
SELECT LENGTH('Tom Smith') -- 9

С помощью дополнительного оператора можно задать где имеено удалить пробелы: BOTH (в начале и в конце), TRAILING (только в конце), LEADING (только в начале):

SELECT TRIM(BOTH FROM ' Tom Smith ')
SELECT LOCATE('om', 'Tom Smith'); -- 2 SELECT LOCATE('m', 'Tom Smith'); -- 3 SELECT LOCATE('m', 'Tom Smith', 4); -- 6 SELECT LOCATE('mig', 'Tom Smith'); -- 0
SELECT SUBSTRING('Galaxy S8 Plus', 8), -- S8 Plus (SELECT SUBSTRING('Galaxy S8 Plus', 8, 2) ); -- S8
SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 1), -- Galaxy (SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 2) ), -- Galaxy S8 (SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', -2) ); -- S8 Plus
SELECT REPLACE('Galaxy S8 Plus', 'S8 Plus', 'Note 8') -- Galaxy Note 8
SELECT INSERT('Galaxy S9', 8, 3, 'Note 9'); -- Galaxy Note 9
SELECT REVERSE('123456789') -- 987654321
SELECT REPEAT('ab', 5); -- ababababab
SELECT LPAD('Tom Smith', 13, '*'); -- ****Tom Smith
SELECT RPAD('Tom Smith', 13, '*'); -- Tom Smith****
CREATE TABLE Products ( Id INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price DECIMAL NOT NULL );

И при извлечении данных применим строковые функции:

SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation, CONCAT(ProductName, ' - ', Manufacturer) AS FullProdName FROM Products ORDER BY Abbreviation

Источник

Оцените статью
© 2023 Программирование