REPLACE string with SUBSTRING function

Question

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()                                   )

Why `SUBSTRING` and not `LEFT` (and `CHARINDEX`)? There's no need for `REPLACE` at all, in truth. As for those results, think about what happens if your string doesn't have `::` in it. `CHARINDEX` returns `0` if the characters aren't found in the string. — Thom A, Dec 06 '22 at 11:54
Does this answer your question? [Get everything after and before certain character in SQL Server](https://stackoverflow.com/questions/11010453/get-everything-after-and-before-certain-character-in-sql-server) — Thom A, Dec 06 '22 at 12:11
@Larnu, I thought to use `CASE WHEN CHARINDEX('::',Col1) <> 0 then REPLACE(...) else Col1`. Should be fine right? — MAK, Dec 06 '22 at 12:12
The *easiest* method in my mind is the add the characters you are looking for at the *end* of the value, @MAK . Then your value *always* has the characters you are looking for; and the characters left of the search string in `'GETDATE()::'` is `'GETDATE()'`. — Thom A, Dec 06 '22 at 12:13
@MAK Just a couple more options which may be a little cleaner ... https://dbfiddle.uk/Kqd0M-pG — John Cappelletti, Dec 06 '22 at 20:12

gotqn · Accepted Answer · 2022-12-07T06:19:19.280

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')
      ,(null)
      ,('')
      ,('getdate()');

SELECT [value]
      ,REPLACE(REPLACE([value],SUBSTRING([value],CHARINDEX('::',[value]),LEN([value])),''),'(''','''') 
      ,IIF(CHARINDEX('::', [value]) > 0, SUBSTRING([value], 0, CHARINDEX('::', [value])), [value])
      -- John Cappellletti's idea
      ,left([value],charindex('::',[value]+'::')-1)
FROM  @DataSource;

Seems like a long way to go. Could be as simple as: left(Col1,charindex('::',Col1+'::')-1) — John Cappelletti, Dec 06 '22 at 20:20

REPLACE string with SUBSTRING function

1 Answers1