Is there a way to edit a substring in SQL?

Question

For example lets say i have a select query:

    SEL SUBSTRING(TXT,5,10) FROM RANDOM_DB.BDAYINFO

and this returns things like

    01DEC99/M/ 

and also

    01DEC9999/

there's data that comes back in both of these formats

What i'm trying to do is get rid of the /M/ at the end of the first one and / at the end of the last one. For now I am focusing on getting rid of the /M/.

What I have tried so far is:

    SEL SUBSTRING(TXT,5,10) CASE WHEN SUBSTRING(TXT,5,10) IS LIKE '%/M/' THEN SEL SUBSTRING(TXT,5,7) FROM RANDOM_DB.BDAYINFO

this is saying that the sub string ends in '/M/' which stands for male in this case

I have also tried using TRIM like so:

   SELECT TRIM('/M/' FROM SUBSTRING(TXT,5,10)) FROM RANDOM_DB.BDAYINFO

Answer 1

With this CASE statement:

SELECT 
  CASE 
    WHEN SUBSTRING(TXT,5,10) LIKE '%/M/' THEN SUBSTRING(TXT,5,7) 
    WHEN SUBSTRING(TXT,5,10) LIKE '%/' THEN SUBSTRING(TXT,5,9) 
    ELSE SUBSTRING(TXT,5,10)
  END
FROM RANDOM_DB.BDAYINFO

Answer 2

Try this method- Using LEFT(Samplevalue,CHARINDEX('/',Samplevalue + '/')-1)

DECLARE @Samples TABLE (Samplevalue VARCHAR(30));
INSERT INTO @Samples VALUES ('01DEC99/M/ '),('01DEC9999/');
SELECT LEFT(Samplevalue,CHARINDEX('/',Samplevalue + '/')-1) AS EXPECTED_RESULT FROM @Samples;