I am working with sql language. I want to select a substring from the string : Rayan.
When I execute, select substring(col1, 1, 5) from table1 it works fine.
My question is, If I give the length of the string more than it exists, it will shown the same result?
ie, select substring(col1, 1, 6) from table1
result : Rayan
Now my need : If we give the length more than the string length, it will not return anything. Anyone have any idea?
Fiddle : http://sqlfiddle.com/#!2/90438/2
You are use substring function. You are specify maximum length.
SUBSTRING ( expression ,start , length )
Arguments
expression Is a character, binary, text, ntext, or image expression.
start Is an integer or bigint expression that specifies where the returned characters start. If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.
length Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.
You can check the length of the column and conditionally show no output in the event that the length you are trying to select (in characters) be greater than this length:
SELECT id,
CASE WHEN CHAR_LENGTH(col1) <= 10 THEN SUBSTRING(col1, 1, 10)
ELSE ''
END AS cond_col1
FROM table1
You can replace the value 10 with whatever makes sense in your use case (you didn't give us this information either in the OP or the Fiddle), e.g. a column.
You can try as per below-
select substring(col1, 1,if(5<=length(col1),5,0))
from table1
