SQL question

[Admiral Huddy]

I'm trying to query a Sybase database, which has an email column. The column may have multiple email addresses separated with a comma. I'd like to count the total amount of emails specified in the column.

So for example, the email column could look like this:

'sid@ email. co.uk'
'harry@fakeemail. co.uk, marry@ anothermail. com'
'julie@ juliemail.co.uk, john@ anothermail.com,[email protected]'

so the count is 6

Thanks

 

[gpuerrilla]

Its a Sum then?

SELECT SUM(formula to ignore the commas,)
AS TotalCount
FROM DBTABLE

 

[chroniclard]

Does sybase have string split? This works in sql, 6 returned total on the given data.

select SUM(number) from (
select emails, COUNT(value) as number from tblTest
cross apply string_split(emails, ',')
GROUP BY
emails) a

 

[hood1971]

sybase has sa_split_list instead of string_split - I think format is the same. Though it depends of version is running.

 

[touch]

You could do it by counting the number of @ symbols in the column:

SELECT SUM(LENGTH([columnname]) - LENGTH(STR_REPLACE([columnname], "@", null)))
FROM table

That takes the number of characters in the email address column then subtracts the number of characters in the column if you remove all the @ symbols so you'll be left with the number of symbols contained in that column.

 

[Admiral Huddy]

I'll give that a try touch.

I tried string_split before realising it wasn't supported.. I'll give sa_split_list a try too.

Cheers

 

[Admiral Huddy]

That worked.. for some reason, my editor didn't like the LENGTH so I changed this for LEN

 

[gpuerrilla]

my editor didn't like the LENGTH

Oof...