Substring
24/09/2004
0
Select * from tabela where substring(...Aqui eu não sei mais), mas era semelhante ao comando copy do delphi(String, Posição, Qde a ser copiada). Alguém sabe, me fiz entender?
Desde já grato
Paulo
Posts
24/09/2004
Motta
SUBSTRING(column_reference FROM start_index [FOR length])
Description
Use SUBSTRING to extract a substring from a character value (column, literal, parameter, or caculated values), specified in the column reference.
FROM is the character position at which the extracted substring starts within the original string. The index for FROM is based on the first character in the source value being 1.
FOR is optional, and specifies the length of the extracted substring. If FOR is omitted, the substring goes from the position specified by FROM to the end of the string.
The example below, applied to the literal string ´ABCDE´ returns the value ´BCD´.
SELECT SUBSTRING(´ABCDE´ FROM 2 FOR 3) AS Sub
FROM Country
In the SELECT statement below only the second and subsequent characters of the NAME column are retrieved. For a column value of ´Belgium´ that would be a ´elgium´ result.
SELECT SUBSTRING(Name FROM 2)
FROM Country
When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
Applicability
SUBSTRING can only be used with character columns or literals. To use on values of other data types, the values must first be converted to CHAR using the CAST function.
Note:the SUBSTRING function cannot be used with memo or BLOB columns.
24/09/2004
Paulo
24/09/2004
Motta
Removes the trailing or leading character, or both, from a string.
TRIM([LEADING|TRAILING|BOTH] [trimmed_char] FROM column_reference)
Description
Use TRIM to delete the leading or trailing character, or both, from a character value (column, literal, parameter, or caculated values). The TRIM function only deletes characters located in the specified position.
The first parameter indicates the position of the character to be deleted, and has one of the following values:
ValueDescription
LEADINGDeletes the character at the left end of the string.
TRAILINGDeletes the character at the right end of the string.
BOTHDeletes the character at both ends of the string.
The trimmed character parameter specifies the character to be deleted, if present. Case-sensitivity is applied for this parameter. To make TRIM case-insensitive, use the UPPER function.
FROM specifies the character value from which to delete the character. The column reference for FROM can be a table column or a character literal.
Example variations:
TRIM syntaxResult
TRIM(LEADING ´_´ FROM ´_ABC_´)´ABC_´
TRIM(TRAILING ´_´ FROM ´_ABC_´)´_ABC´
TRIM(BOTH ´_´ FROM ´_ABC_´)´ABC´
TRIM(BOTH ´A´ FROM ´ABC´)´BC´
When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
Applicability
TRIM can only be used with character columns or literals. To use on values of other data types, the values must first be converted to CHAR using the CAST function.
Note:the TRIM function cannot be used with memo or BLOB columns.
fonte localsql.hlp (acha que fica na pasta de help do Delphi)
24/09/2004
Paulo
24/09/2004
Motta
24/09/2004
Keitarosan
UPDATE tabela SET campo = TRIM(campo);
Com essa instrução você vai aplicar um TRIM em todos os 15 mil registros... a sintaxe do TRIM no MySQL é igualzinha do Delphi :wink:
24/09/2004
Paulo_amorim
Olá
Só para referencia: quando tiver uma dúvida sobre sintaxe de alguma função do MySQL eh soh entrar no site (mysql.com) e entrar no help... lá ele traz rapidinho a sintaxe, como no Delphi
Até+
Clique aqui para fazer login e interagir na Comunidade :)