The following illustrates the syntax of the substring function with POSIX regular expression: SUBSTRING(string FROM pattern) Code language: SQL (Structured Query Language) ( sql ) In addition to the SQL-standard substring function, PostgreSQL allows you to use extract a substring that matches a POSIX regular expression. Last_name Code language: SQL (Structured Query Language) ( sql ) Extracting substring matching POSIX regular expression SUBSTRING( first_name, 1, 1 ) AS initial FROM We select last_name and first_name column. We get the initial name by extracting the first character of the first_name column. In the following example, we query data from the customer table. The results are the same as the one in the first example. See the following example: SELECT SUBSTRING ( 'PostgreSQL' FROM 1 FOR 8) - PostgreS SELECT SUBSTRING ( 'PostgreSQL' FROM 8) - SQL Code language: SQL (Structured Query Language) ( sql ) In this form, PostgreSQL puts three parameters into one.
PostgreSQL provides another syntax of the substring function as follows: substring(string from start_position for length) Code language: SQL (Structured Query Language) ( sql ) The substring is a string beginning at 8, which is SQL. In the second statement, we extract a substring started at position 8 and we omit the length parameter. In the first statement, we extract a substring that has length of 8 and it is started at the first character of the PostgreSQL string. See the following examples: SELECT SUBSTRING ( 'PostgreSQL', 1, 8) - PostgreS SELECT SUBSTRING ( 'PostgreSQL', 8) - SQL Code language: SQL (Structured Query Language) ( sql ) If you omit the length parameter, the substring function returns the whole string started at start_position. If the sum of start_position and length is greater than the number of characters in the string, the substring function returns the whole string beginning at start_position. length is a positive integer that determines the number of characters that you want to extract from the string beginning at start_position.Though in other database systems such as MySQL the substring function can accept a negative start_position. If start_position equals zero, the substring starts at the first character of the string. start_position is an integer that specifies where you want to extract the substring.
string is a string whose data type is char, varchar, text, etc.The following illustrates the syntax of the substring function: SUBSTRING ( string ,start_position, length ) Code language: SQL (Structured Query Language) ( sql ) The substring function returns a part of string. Introduction to PostgreSQL substring function Summary: in this tutorial, we will introduce you to PostgreSQL substring function that extracts a substring from a string.
0 kommentar(er)
