CODEWITHSUNDEEP
sqlsql-server
user8834780
user8834780

Reputation: 1670

Specify multiple characters for substring condition

As per answer in String Split into column instead of rows, I noticed that sometimes XmlEmail.value('/Emails[1]/email[1]','varchar(100)') AS Email1 returns multiple emails that are separated either by a space (\s) or by a semi-colon (;). This is the way they were inputed- nothing wrong with query.

I was trying to only take value before that space, so this is what I came up with:

CASE CHARINDEX(' ', LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)')), 1)
WHEN 0 THEN LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)'))
ELSE SUBSTRING(LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)')), 1, CHARINDEX(' ',LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)')), 1) - 1) end as Email_1

However, I can't do that because I also have cases with ';'.

Any idea how can I implement both?

Upvotes: 0

Views: 93

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

You can still use the XML approach. The only trick is to "Clean" the string first.

This little trick Gordon demonstrated several months ago. I can't find the original link, but it will reduce multiple spaces and/or semi-colons down to one (expand, reduce, and finally eliminates). Simply brilliant, only wish I could take credit for it.

Example

Declare @t table (Id INT, email_address VARCHAR(1000) , email_new VARCHAR(100));
INSERT INTO @t VALUES
(1,'[email protected]', '[email protected]'),
(2,'[email protected];;;;   [email protected]', '[email protected]'      ),
(3,'[email protected]  [email protected]; [email protected]','[email protected]'               )

Select A.ID
      ,C.*
 From  @t A
 Cross Apply (values (replace(replace(replace(replace(email_address,' ','<>'),';','<>'),'><',''),'<>',';'))
             ) B(CleanString)
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                From  (Select Cast('<x>' + replace(CleanString,';','</x><x>')+'</x>' as xml) as xDim) as A 
             ) C

Returns

ID  Pos1                        Pos2                    Pos3    Pos4    Pos5
1   [email protected]  NULL                    NULL    NULL    NULL
2   [email protected]        [email protected]    NULL    NULL    NULL
3   [email protected]                 [email protected]             [email protected]    NULL    NULL

Upvotes: 2

Thom A
Thom A

Reputation: 96003

Use PATINDEX instead. With no data to test, I can't say this'll work, however, it'll probably be something like:

CASE PATINDEX('%[ ;]%', LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)')))
WHEN 0 THEN LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)'))
ELSE SUBSTRING(LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)')), 1, PATINDEX('%[ ;]%',LTRIM(XmlEmail.value('/Emails[1]/email[1]','varchar(100)'))) - 1) end as Email_1

OP says this doesn't work, however, it does seem to work for me:

DECLARE @Email varchar(500) = '[email protected] [email protected]';

SELECT
CASE PATINDEX('%[ ;]%', LTRIM(@Email))
WHEN 0 THEN LTRIM(@Email)
ELSE SUBSTRING(LTRIM(@Email), 1, PATINDEX('%[ ;]%',LTRIM(@Email)) - 1) end as Email_1;

Returns: [email protected]

DECLARE @Email varchar(500) = '[email protected];[email protected]';

SELECT
CASE PATINDEX('%[ ;]%', LTRIM(@Email))
WHEN 0 THEN LTRIM(@Email)
ELSE SUBSTRING(LTRIM(@Email), 1, PATINDEX('%[ ;]%',LTRIM(@Email)) - 1) end as Email_1;

Also returns: [email protected]

I can't, however, run this against an xml, as (again), I don't have any sample data of that format from the OP.

Upvotes: 1

Related Questions