Find and replace before and after using an sql joker

Question

Find and replace before and after using an sql joker

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I have a list of over 5000 letters with different names and a - (hyphen) in common. Example:

Evilswarm Exciton Knight LVAL-EN056   
Number 101: Silent Honor ARK LVAL-E47  
Number 11: Big Eye GAV-EN090  
Lavalval Chain HA07-EN019

What I would like to do is to replace or delete from the hyphen X characters after and before, so the result looks like this:

Lavalval Chain -

I was using

UPDATE cartas SET nome=LEFT(nome, LEN(nome)-5)

but the sizes are different.

I do not need to run both functions at the same time, so if you have any idea of just deleting 1 character before - , you'll already give me a base how to do it.

sql

asked by anonymous 31.03.2014 / 20:03

2 answers

Function to clean html Chrome notification

score 3 · Answer 1

This is a very simple task to solve using traditional functions. Here are two examples for two common SQL dialects, but for sure you can adapt to just about any version and language.

The secret is in the function that locates a string within the other, and returns the position numerically:

First, we run this select to get the string up to - :

UPDATE cartas SET nome = IIF(
   CHARINDEX( nome, '-' ) = 0,
   nome,
   LEFT( nome, CHARINDEX( nome, '-' ) )
);

Now, Number 11: Big Eye GAV-EN090 is only Number 11: Big Eye GAV- .

Next, we use the second query to just pick up the space before GAV- :

UPDATE cartas SET nome = IIF(
   CHARINDEX( REVERSE(nome), ' ' ) = 0,
   "",
   REVERSE( SUBSTRING( REVERSE(nome), CHARINDEX( REVERSE(nome), ' ' ) + 1 ) )
);

We had Number 11: Big Eye GAV- and now we have Number 11: Big Eye !

In T-SQL use CHARINDEX() , IIF() and SUBSTRING() , in MySQL use INSTR() , IF() and SUBSTR()

Functions used:

IIF(condicao,seVerdadeiro,seFalso) depending on the condition returns one of two values;
CHARINDEX(palheiro, agulha) returns the numeric position of agulha within palheiro ;
LEFT(string, quantidade) returns quantidade of initial characters of string ;
REVERSE(string) inverts the string, transforming abc into bca .
SUBSTRING(string,inicio,quantidade) returns the number of characters in the string from the start position.

score 0 · Answer 2

I ended up using the regular expressions of excel where I searched with ... -... and replaced with-, it worked fine, then I imported it back to the database, then I will search if sql has it and how to use it anyway thanks comments and answers.

For those who want to know about regular expression in Broffice follow the link

link