1 / 41

Built-in Functions. Usage of Wildcards

Built-in Functions. Usage of Wildcards. Most important built-in functions. How to take advantage of wildcards?. Databases Basics. SoftUni Team. Technical Trainers. Software University. http://softuni.bg. Table of Contents . String built in functions SQL Server/MySQL

lyneth
Download Presentation

Built-in Functions. Usage of Wildcards

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Built-in Functions.Usage of Wildcards Most important built-in functions. How to take advantage of wildcards? Databases Basics SoftUni Team Technical Trainers Software University http://softuni.bg

  2. Table of Contents • String built in functions SQL Server/MySQL • Math built in functions SQL Server/MySQL • Date built in functions SQL Server/MySQL • Other built in functions SQL Server/MySQL • Like + Wildcards

  3. Questions sli.do#8594

  4. String Functions

  5. LEN MySQL LEN(ColumnName) LENGTH(column_name) CHAR_LENGTH(column_name) SQL Server

  6. CONCAT SQL Server/MySQL CONCAT(param1, param2, …, paramN)

  7. LOWER / UPPER SQL Server/MySQL LOWER/UPPER(ColumnName)

  8. LEFT / RIGHT LEFT/RIGHT (ColumnName, TakeQuantity) SQL Server/MySQL

  9. LTRIM / RTRIM SQL Server/MySQL LTRIM/RTRIM(ColumnName) MySQL has TRIM function!

  10. SUBSTRING SQL Server/MySQL SUBSTRING(ColumnName, StartIndex, Length)

  11. CHARINDEX MySQL LOCATE(substr, str, position) CHARINDEX(substr, str, [position]) SQL Server

  12. REPLACE REPLACE(StringExpression, StringPattern, StringReplacement) SQL Server/MySQL

  13. REPLICATE MySQL REPLICATE(ColumnName, NumberOfTimes) REPEAT(column_name, number_of_times) SQL Server

  14. REVERSE REVERSE(ColumnName) SQL Server/MySQL

  15. STUFF / INSERT MySQL INSERT(column_name, start_index, length, insert_str) STUFF(ColumnName, StartIndex, Length, InsertStr) SQL Server

  16. Math Functions

  17. PI SQL Server/MySQL PI()

  18. ABS SQL Server/MySQL ABS(Number)

  19. FLOOR / CEILING FLOOR/CEILING(number) SQL Server/MySQL

  20. POWER MySQL POWER(NumberToRaise, Power) POW(number_to_raise, power) SQL Server

  21. Random RAND() RAND(Seed) SQL Server/MySQL

  22. ROUND ROUND(Number, Precision) SQL Server/MySQL

  23. SIGN SIGN(Number) SQL Server/MySQL

  24. Square Root SQL Server/MySQL SQRT(Number)

  25. Date Functions

  26. Date Addition MySQL DATEADD( DatePart, Quantity, ColumnName) DATE_ADD( column_name, INTERVAL quantity date_part) SQL Server

  27. Date Difference MySQL DATEDIFF( DatePart, FirstDate, SecondDate) DATEDIFF( first_date, second_date) SQL Server

  28. Date Name MySQL DATENAME( DatePart, Date) DAYNAME(date) SQL Server

  29. Day / Month / Year MySQL DAY / MONTH / YEAR(Date) DAY / DAYOFMONTH / DAYOFYEAR(date) SQL Server

  30. Current Time MySQL GETDATE() NOW() SQL Server

  31. Other Functions

  32. Cast CAST (expression AS type) SQL Server/MySQL

  33. Conversion MySQL CONVERT(expr, type) CONVERT(type, expr) SQL Server

  34. Null Check MySQL ISNULL( CheckExpression, ReplacementValue) IFNULL( check_expression, replacement_value) SQL Server

  35. Wildcards

  36. Using LIKE syntax SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameLIKEpattern;

  37. Wildcards %- A substitute for zero or more characters _ - A substitute for a single character [charlist] –Sets and ranges of characters to match [^charlist] or [!charlist] – Matches only a character not in the brackets (Not working in MYSQL)

  38. Summary There are a lot of built in functions that can help us make some programmable logic inside the database. There are some differences between the SQL Server and the MySQL functions, but almost all of themhave their equivalent in one way or another.

  39. Built-in Functions https://softuni.bg/courses/

  40. License This course (slides, examples, demos, videos, homework, etc.)is licensed under the "Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International" license • Attribution: this work may contain portions from • "Databases" course by Telerik Academy under CC-BY-NC-SA license

  41. Free Trainings @ Software University • Software University Foundation – softuni.org • Software University – High-Quality Education, Profession and Job for Software Developers • softuni.bg • Software University @ Facebook • facebook.com/SoftwareUniversity • Software University @ YouTube • youtube.com/SoftwareUniversity • Software University Forums – forum.softuni.bg

More Related