How to Remove the First Characters of a Specific Column in a Table

How do I remove the first characters of a specific column in a table?

SELECT RIGHT(MyColumn, LEN(MyColumn) - 4) AS MyTrimmedColumn

Edit:
To explain, RIGHT takes 2 arguments - the string (or column) to operate on, and the number of characters to return (starting at the "right" side of the string). LEN returns the length of the column data, and we subtract four so that our RIGHT function leaves the leftmost 4 characters "behind".

Hope this makes sense.

Edit again - I just read Andrew's response, and he may very well have interperpereted correctly, and I might be mistaken. If this is the case (and you want to UPDATE the table rather than just return doctored results), you can do this:

UPDATE MyTable
SET MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 4)

He's on the right track, but his solution will keep the 4 characters at the start of the string, rather than discarding said 4 characters.

How to remove the 1st character from a column in SQL Server

UPDATE YourTable
SET    YourCol = SUBSTRING(YourCol, 2, 0+0x7fffffff) 
WHERE YourCol LIKE ',%'

How to remove the first three characters from every row in a column in R

You can do it with gsub function and simple regex. Here is the code:

# Fake data frame
df <- data.frame(text_col = c("abcd", "abcde", "abcdef"))
df$text_col <- as.character(df$text_col)

# Replace first 3 chracters with empty string ""
df$text_col <- gsub("^.{0,3}", "", df$text_col)

Removing first 'G' character of entire column values in table if it is starting from 'G'

Based on your description, you can use regexp_replace():

select regexp_replace('G12345', 'G', '', 1, 1)

If you only want to remove a 'G' at the beginning of the string, you can use '^G' for the pattern.

Based on your data, you can just use replace():

select replace('G12345', 'G', '')

This removes all 'G's. But your data only seems to have one.

For an update you would just include the logic as an update:

update t
    set col = replace(col, 'G', '')
    where col like '%G%';

Or whichever of the above functions is what you really want to do.

How to remove first few characters from R column values?

The regular expression you are looking for is ".*?\\|".

• . matches all characters
• * zero or more times
• ? make * 'lazy'
• \\| match "|" which is also a regular expression so it must be escaped

Test:

df <- data.frame(col1 = c("INV | Building One", 
                          "BO | Building Twenty Five",
                          "VC | Corporate"))

sub(".*?\\|", "", df$col1)
#> [1] " Building One"         " Building Twenty Five" " Corporate"

Here is a brilliant regex cheatsheet I use for this kind of stuff: https://rstudio.com/wp-content/uploads/2016/09/RegExCheatsheet.pdf

BTW: tidyr comes with a nice little function that would help here:

library(tidyr)
df %>% 
  separate(col1, into = c("col1", "col2"), sep = "\\|")
#>   col1                  col2
#> 1 INV           Building One
#> 2  BO   Building Twenty Five
#> 3  VC              Corporate

It splits your one column into two, which seems plausible here.

How to remove the first character if it is a specific character in SQL

While all other answer are probably also working, I'd suggest to try and use STUFF function to easily replace a part of the string.

UPDATE Telephone
SET number = STUFF(number,1,1,'')
WHERE number LIKE '9%'

SQLFiddle DEMO

How to remove the first characters in a column

A good hint is provided here:

UPDATE your_table
SET column2 = TRIM(LEADING '0' FROM column1)

supposing that the original value is stored in column1 and you want to write the 0-trimmed one in column2

Remove first x number of characters from each row in a column of a Python dataframe

Use vectorised str methods to slice each string entry

In [11]:
d['Report Number'] = d['Report Number'].str[3:]
d

Out[11]:
     Name Report Number
0  George       1234567
1    Bill       9876543
2   Sally       4434555

Removing the first 5 characters of each entry of a column STORED PROCEDURE

Do you really want to delete the first 5 characters of the string, or do you want to delete everything up to the first forward slash?

The other answers here address your literal question. This answers the question you might really be asking (PostgreSQL dialect):

select substr(campground,position('/' in campground)+1)
from (values
     ('ABC/American Bike Camp')
    ,('ALBE/Alberta Beach Family RV Park')) t(campground)
;

            substr
------------------------------
 American Bike Camp
 Alberta Beach Family RV Park
(2 rows)

Using that expression in an update statement:

update reservation
set campground = substr(campground,position('/' in campground)+1)
where campground like '%/%'
;

(where clause included to ensure it doesn't update rows that have already been updated)

---

Related Topics