Find and replace before and after using an sql wildcard

Asked

Viewed 2,326 times

1

I have a list of over 5000 cards with different names and one - (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 replace or delete from the hyphen X characters after and before, so the result looks like this:

Lavalval Chain -

Was using 

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

but the sizes are different.

I don’t need to run both functions at the same time if you have any idea of only deleting 1 character before the - will already give me a basis of how to do.

sql
  • 1

    Have you tried using regular expressions? In Postgresql you have the function regexp_replace and you will find the explanations in: http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

  • I ended up using the expressions in excel I removed and upei the bank again, but it was this way even, as postgresql never messed with it, anyway worth

  • 1

    @Arsomnolasco saw that you solved with regex, but anyway updated the answer according to the statement, using SQL.

2 answers

3

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

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

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

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

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

Then we use the second query to only take up the space before the GAV-:

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

We had it made 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 the two values;
  • CHARINDEX(palheiro, agulha) returns the numeric position of the agulha within the palheiro;
  • LEFT(string, quantidade) returns to quantidade of initial characters of string;
  • REVERSE(string) reverses the string, transforming abc in bca.
  • SUBSTRING(string,inicio,quantidade) returns the number of characters in the string from the start position.

0

I ended up using the regular expressions of excel where I searched with ...-... and replaced with -, it worked well , then I imported it again to the database, then I will search if sql has this and how to use anyway was worth there to comments and answers.

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

https://help.libreoffice.org/Common/List_of_Regular_Expressions/pt-BR

  • 2

    could elaborate a little better your answer, so that other people who face the same problem in the future can use your solution?

  • 2 days to vote in favor, as for the preparation of the answer I think I was quite direct, reviewed, and for those who know regular expression just go in the famous crtl + f = find(locate) and search using the expressions anyway it does not cost to pass the link

  • 1

    Forgive my ignorance, but what Broffice has to do with SQL?

  • it exports and imports sql tables

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.