how to select characters after first 20 characters

问题:

 select address (first 20 character) as Address1 , 
        address (characters after first 20 if less then 20 then NULL) as Address2
 from customer

How do I select string after 20 characters?

回答1:

To get characters after the first 20 characters (note that if there are not twenty characters, the function will return an empty string):

SELECT SUBSTRING('Some Random Address That is Longer than 20 characters' FROM 20);

Now if you need address 2 to be NULL, you check character length first:

SELECT if(char_length(address) > 20, SUBSTRING(address FROM 20), NULL);

To get the first 20 characters, you can use the substring function like this:

SELECT SUBSTRING('Some Random Address', 1, 20);

Now the final query could look like this:

SELECT SUBSTRING(address, 1, 20) as Address1, 
    IF(CHAR_LENGTH(address) > 20, SUBSTRING(address FROM 20), NULL) as Address2
FROM customer

回答2:

The substring is what you're looking for:

SELECT SUBSTRING(address, 1, 20) AS Address1, 
       SUBSTRING(address FROM 20) AS Address2
FROM   customer