REPLACE string with SUBSTRING function-CodePudding

Given input data:

Col1                                        
---------------------------------------
'-'::"varchar" COLLATE "default"            
'-1'::integer                               
'0'::smallint                               
'1'::"varchar" COLLATE "default"            
(get_val())::"timestamp"    
0                                           
0.0                                         
10                                          
210                                         
90000                                       
getdate()

I'm trying to replace the part of the string(column Col1) to empty string ''.

Want to replace anything after :: with empty string as shown below in the expected result.

Expected Result:

Col1                                        Col2
------------------------------------------------------------------------
'-'::"varchar" COLLATE "default"            '-'
'-1'::integer                               '-1'
'0'::smallint                               '0'
'1'::"varchar" COLLATE "default"            '1'
(get_val())::"timestamp"                    (get_val())
0                                           0
0.0                                         0.0
10                                          10
210                                         210
90000                                       90000
8                                           8
getdate()                                   getdate()

My try:

SELECT Col1  REPLACE(REPLACE(Col1,SUBSTRING(Col1,CHARINDEX('::',Col1),LENGTH(Col1)),''),'(''','''') 
FROM  tbl_string_pattern;

But getting output like:

Col1                                        Col2
------------------------------------------------------------------------
'-'::"varchar" COLLATE "default"            '-'
'-1'::integer                               '-1'
'0'::smallint                               '0'
'1'::"varchar" COLLATE "default"            '1'
(get_val())::"timestamp"                    (get_val())
0                                           0
0.0                                         0
10                                          0
210                                         0
90000                                       0
8                                           8
getdate()                                   )

CodePudding user response：

Try this:

DECLARE @DataSource TABLE
(
    [value] VARCHAR(128)
);

INSERT INTO @DataSource ([value])
VALUES ('''-''::"varchar" COLLATE "default"')
      ,('''-1''::integer')
      ,('''0''::smallint')
      ,('''1''::"varchar" COLLATE "default"')
      ,('(get_val())::"timestamp" ')
      ,('0')
      ,('0.0')
      ,('10 ')
      ,('210')
      ,('90000')
      ,('8')
      ,('getdate()');

SELECT [value]
      ,REPLACE(REPLACE([value],SUBSTRING([value],CHARINDEX('::',[value]),LEN([value])),''),'(''','''') 
      ,IIF(CHARINDEX('::', [value]) > 0, SUBSTRING([value], 0, CHARINDEX('::', [value])), [value])
FROM  @DataSource;