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?

  • 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 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 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 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.