-3

See image.

I have a table full of records relating to specific departments. If the department name is held under the field 'StandardWork' but after the word 'Support -' what is the SQL command to create a new column called Service which will disregard the word support, the space and the dash and just use the department to the right?

enter image description here

JsonStatham
  • 9,770
  • 27
  • 100
  • 181
  • 2
    A permanent column to replace "standardWork"? or co-existing? – gbn Nov 23 '11 at 11:38
  • 2
    That image is really hard to look at. – Brian Nov 23 '11 at 11:39
  • a co-existing colum which is taking the department name from StandardWork – JsonStatham Nov 23 '11 at 11:40
  • @SQL_Surfer: why not use a computed column then like I suggested? Your choice of accepted answer doesn't match your stated requirement... – gbn Nov 23 '11 at 12:34
  • The answer I accepted worked for me, it kept the original column and also gave me a new one, I wanted to accept all as they all worked but i was only allowed to accept one so I decided on that one as it was the first i tried. Sorry – JsonStatham Nov 23 '11 at 13:25

4 Answers4

2

If StandardWork need to exist alongside Service, computed column

ALTER TABLE Whatever
   ADD [Service] AS SUBSTRING(StandardWork, 11, 8000);

If to replace StandardWork, then:

ALTER TABLE Whatever
   ADD [Service] varchar(200) NULL;

UPDATE Whatever SET [Service] = SUBSTRING(StandardWork, 11, 8000);

ALTER TABLE Whatever
   ALTER COLUMN [Service] varchar(200) NOT NULL;

ALTER TABLE Whatever
   DROP COLUMN StandardWork;
gbn
  • 422,506
  • 82
  • 585
  • 676
2

I prefer to use the STUFF function to solve these issues.

    select stuff(StandardWork, 1, 10, '')

This function will delete the characters 1-10 and replace them with an empty string.

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
1

You can use replace

select replace(StandardWork, 'Support - ', '')

or stuff if you have departments that has Support - as a part of the name.

select stuff(StandardWork, 1, 10, '')
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    That will break when StandardWork contains `'Support - something - Support - something else'` – Filip De Vos Nov 23 '11 at 11:39
  • @FilipDeVos - Break is a bit harsh :). But it will certainly replace **all** instances of `'Support - '` in `StandardWork`. Updated answer with another way to do it. – Mikael Eriksson Nov 23 '11 at 11:43
1

Inside a select statement:

select ...
       case when StandardWork like 'Support - %' 
            then replace(StandardWork, 'Support - ', '')
       end SupportSubCategory
       ...