Substring
Há muito tempo eu aprendi a fazer uma instrução em sql, usando substring. Esta instrução era para contralar os finais das placas dos carros, para saber se estava na época de renovar ou não. Só que eu nunca mais usei, e agora eu preciso, não sei mais. Era mais ou menos assim:
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
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
Curtidas 0
Respostas
Motta
24/09/2004
MANUAL DO PARADOX
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.
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.
GOSTEI 0
Paulo
24/09/2004
Motta, é exatamente isso. Valeu!!
GOSTEI 0
Paulo
24/09/2004
Por exemplo, motta, se eu quero excluir somente os espaços em brancos no início de uma string, é possível com substring?
GOSTEI 0
Motta
24/09/2004
TRIM function
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)
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)
GOSTEI 0
Paulo
24/09/2004
Motta, uso Mysql. Qual é a sintaxe do comando trim. Na minha tabela existem vários campos com o primeiro e alguns o até o segundo caracter, em branco. Como eu altero toda a minha tabela? São 15.000 registros, dos quais deve haver uns 5.000 assim, errados. Qual a sintaxe que eu utilizo com o Trim? É possível pelo Mysql-Front?
GOSTEI 0
Motta
24/09/2004
Não manjo nada de Mysql, mas tem um forum aqui, e sites especializados.
GOSTEI 0
Keitarosan
24/09/2004
Faça o sequinte rapaz, testei isso no MySQL 4.0.18 e no MySQL 5.0.1:
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:
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:
GOSTEI 0
Paulo_amorim
24/09/2004
Motta, uso Mysql. Qual é a sintaxe do comando trim. Na minha tabela existem vários campos com o primeiro e alguns o até o segundo caracter, em branco. Como eu altero toda a minha tabela? São 15.000 registros, dos quais deve haver uns 5.000 assim, errados. Qual a sintaxe que eu utilizo com o Trim? É possível pelo Mysql-Front?
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é+
GOSTEI 0