T/SQL - String Manipulation

Question

I have the below query.

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

Please post your code/problem here, do not make us go to another site to see it. There's nothing wrong with giving us a SQLFiddle, but don't use it as a replacement for describing your code and problem here. — RBarryYoung
– RBarryYoung, Commented Nov 12, 2013 at 17:46
sry, I always post my code here but seen many people use SQLFiddle so I thought I would try that this time. :facepalm: — 007
– 007, Commented Nov 12, 2013 at 17:49

Bogdan Bogdanov · Accepted Answer · 2013-11-12 18:06:47Z

2

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 '-'.

edited Nov 12, 2013 at 18:06

answered Nov 12, 2013 at 18:01

Bogdan Bogdanov

1,7232 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

007 Over a year ago

This works as required. Thanks for the extra explanation too, your approach is very "ah-ha!" like...at least to me. :)

Bogdan Bogdanov Over a year ago

Yes, I enjoy your question :)

Collectives™ on Stack Overflow

T/SQL - String Manipulation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related