0

I have a db table with more than 200 rows (table name: ports).

There is this column (port name). The data in the column is like: aaa 1/15.

I want to edit all the data in this and turn the aaa 1/15 to 1/15.

Of course the column value differs. One ends with 1/15 The other with 2/18.

So the point is, I want to get rid of all the aaa in that column for all rows. Is there a way to do it?

3
  • 1
    I want to get rid of all the aaa in that column for all rows Does all column values have the same prefix 'aaa '? if not - what is criteria of a border between part to be removed and part to be stored? space? last space? something else?
    – Akina
    Oct 15 '19 at 10:47
  • 1
    On the same line of thinking, are you able to safely say that exactly the last 4 characters of the column are the only ones you want to keep?
    – Jacob H
    Oct 15 '19 at 10:52
  • Thank you for the replies. It goes like this "aaa - 1/15". And the last part i want to get is either 3 or 4 characters. Ex: 1/2 or 1/12
    – user19215
    Oct 15 '19 at 11:00
2

last part i want to get is either 3 or 4 characters. Ex: '1/2' or '1/12'

UPDATE [TableName]
SET [ColumnName] = LTRIM(RIGHT([ColumnName], 4))
1

There are a couple of options, depending on the state of your data. If the prefix is always 'aaa', then do this:

UPDATE [TableName]
SET [ColumnName] = LTRIM(RTRIM(REPLACE([ColumnName], 'aaa', '')))

If the prefix changes, but it is always the last 4 characters you want to retain, then do this:

UPDATE [TableName]
SET [ColumnName] = SUBSTRING([ColumnName], LEN([ColumnName])-4, 5)

If the prefix and length varies, but is always separated by a space (and this is the only space in the field) you can do this:

UPDATE [TableName]
SET [ColumnName] = SUBSTRING([ColumnName], CHARINDEX(' ', [ColumnName]), LEN([ColumnName])-CHARINDEX(' ', [ColumnName])+1)

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .