How to remove the first character from a string in a Query

Asked

Viewed 5,477 times

2

In the Postgresql 9.2 I have a column with the following information:

A101
B12
C3

I need one command to select and another to update this column by removing the first character thus:

101
12
3

I tried to solve with function substring, but I could not reach the expected result.

How to solve?

sql postgresql

  • Try this query: UPDATE tabela SET coluna = RIGHT(coluna, LEN(coluna) - 1). This should update the column.

  • @Dvdsamm, this command is for SQL Server, my SGDB is Postgresql, still thank you.

2 answers

4


As you passed that your column from which you do not want the first character, so let’s consider any and all character types.

To select the data by removing the first character, and using the SUBSTR, we can do it this way:

SELECT SUBSTR(info,2,LENGTH(info)) 
  FROM INFORMACAO

Example: http://sqlfiddle.com/#! 15/6d7c4c/9

Substar function:
substr(string, from [, count])
The first parameter being the text, the second the beginning of the characters of the text, and the third the total of characters.

So, then we pass that you need to take from the second character, up to the full size of the text, we get this through the function lenght, that calculates the total of characters in a text.

Now to update to the column, we use the same functions cited:

UPDATE informacao 
   SET info = SUBSTR(info,2, LENGTH(INFO));

There are other ways to do what you expect, but I did it using the substr which was quoted in the question, to have a greater learning.

  • 1

    It worked perfectly, thank you very much.

-1

The right thing would be:

SELECT SUBSTR(info,2,LENGTH(info)**-1**) 
  FROM INFORMACAO

Browser other questions tagged sql postgresql

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