Split substring in string

Question

I have emails records which includes characters like 'nr' and some number with it like examplenr@example.comnr122342 Here first nr is okay that , but after .com I want to get rid of this 'nr122342' part. I want to use SQL query to get rid of this to get only the original email address. Please advise with appropriate SQL command.

score 0 · Answer 1 · answered May 24 '19 at 14:31

0

You can use LEFT and CHARINDEX; CHARINDEX function returns the position of the expression you are looking, in your case 'nr'.

declare @txt as varchar(50) = 'example@example.comnr122342'

select LEFT(@txt, CHARINDEX('nr', @txt, 1)-1)

answered May 24 '19 at 14:31

Valerica

1,618
1
13
20

Thanks for the quick response Valerica, I have edited the question. I am in Presto environment and unfortunately I can't use I CHARINDEX. – juninho10 May 24 '19 at 14:39
sorry mate ... Unfortunately I can't help you with that. Maybe if you google the equivalent of CHARINDEX function in presto you will find something :) – Valerica May 24 '19 at 15:26

score 0 · Answer 2 · answered May 24 '19 at 14:38

0

Using STUFF and CHARINDEX, this can be possible:

SEELCT STUFF(Email, CHARINDEX('.com', Email, 1) + LEN('.com'), LEN(Email), '') AS Email
FROM TableName

answered May 24 '19 at 14:38

Arulkumar

12,966
14
47
68

score 0 · Answer 3 · answered May 24 '19 at 14:43

you could try somthing like the following:

   CREATE FUNCTION dbo.StringBeforeLastIndex(@source nvarchar(80), @pattern char)
   RETURNS nvarchar(80)
   BEGIN  
         DECLARE @lastIndex int
         SET @lastIndex = (LEN(@source)) -  CHARINDEX(@pattern, REVERSE(@source)) 

       RETURN SUBSTRING(@source, 0, @lastindex + 1) 

  END;  
  GO

score 0 · Answer 4 · answered May 24 '19 at 20:23

You can use the regexp_extract function to extract everything in the string except for the last portion (nr followed by a number)

WITH data(x) AS (VALUES 'examplenr@example.comnr122342')
SELECT regexp_extract(x, '(.+)nr\d+$', 1)
FROM data

produces:

         _col0
-----------------------
 examplenr@example.com
(1 row)

Split substring in string

4 Answers4