CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
007
007

Reputation: 2186

T/SQL - String Manipulation

I have the below query.

SQLFiddle

I can only have possible 7 characters (- and A-Z combined)

There can only be ONE "-" for first 5 characters (Monday to Friday) For Saturday and Sunday, we can only have one character or dash I am replacing Sa and Su with just S

However, whenever they are passing in TWO dashes for Saturday AND/OR Sunday, I need to replace them with ONE dash for each

so length can only be 7 after all manipulations.

I have tried w/e I could but getting stuck at the two vs one dash scenario for Saturday/Sunday position.

Please help! I will keep this updated as I find more.

THX in ADV

Code:

CREATE Table TempTable (string varchar(50))

INSERT INTO TempTable (string)
VALUES ('MTWRFSS')
       ,('MTWRFSaS')
       ,('MTWRFSaSu')
       ,('----F--')
       ,('----F----')
       ,('MT------')
       ,('MT------')
       ,('----FSa--')
       ,('----FSa-')
       ,('----FS--')
       ,('----FS-')
       ,('----F-Su')
       ,('----F--Su')
       ,('----F-S')
       ,('----F--S')

UPDATE TempTable
SET string =  REPLACE(REPLACE(RTRIM(LTRIM(string)),'SA','S'),'SU','S')

SELECT string 
       ,LEN(String) AS stringLengh FROM TempTable

--DROP TABLE TempTable

Upvotes: 3

Views: 104

Answers (1)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

Try to manipulate only characters after 5th, because from MON to FRY you always have 1 -. So I think this will work:

SELECT 
  string as InitialString
  ,LEFT(LEFT(String,5) + replace(replace(replace(RIGHT(String,LEN(String)-5),
  'Sa','S'),'Su','S'),'--','-') + '--',7) as FinalString
FROM TempTable;

You have to cut string into 2: left 5 and the rest. Then using several replaces you can have correct Sat/Sun combination. Concatenate both and you will have final decision. Also 2 more dashes have to be added and the you have to take only LEFT 7, because if you have '--' it will be replaced with '-'.

Upvotes: 2

Related Questions