Post reply

How can I count any character in a column?

  • September 9, 2009 at 4:32 am

    #156191

    If a column keeps email address look like these

    p@hotmail.com;a@hotmail.com

    I'd like count '@' in the column

    How can I write TSQL just only one statement(without loop) to do that ?

  • September 9, 2009 at 4:57 am

    #1050974

    Try this

    declare @String varchar(255)

    Select @String = 'dsasda@fff@ffff@';

    with Nums(n)AS (

    select top 100 percent number from

    master..spt_values

    where

    TYPE='p' and number =1

    order by number

    ),

    CharList(n,CharAT)

    as

    (

    Select n,substring(@String,n,1)

    from Nums

    )

    select count(*) from CharList where CharAt = '@'


    Clear Sky SQL
    My Blog[/url]

  • September 9, 2009 at 5:04 am

    #1050978

    Not sure if this will serve the purpose!

    Select Len('SQLSERVER') - LEN(REPLACE('SQLSERVER','S', ''))

    ---------------------------------------------------------------------------------

  • September 9, 2009 at 5:05 am

    #1050979

    SELECT LEN(@String)-LEN(REPLACE(@String,'@',''))

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • September 9, 2009 at 7:28 am

    #1051086

    Check this,

    declare @STR varchar(100)

    set @STR='mail1@hotmail.com; mail2@hotmail.com'

    select LEN(@str) - LEN(REPLACE(@str, '@', ''))

  • September 9, 2009 at 7:29 am

    #1051088

    Hi,

    You can achieve the same by this query.

    DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(100))

    INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;tejas@SQLY@oga.com'

    INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;'

    ;WITH CTE AS(

    SELECT ID,

    SUBSTRING(data,0, LEN(data)+1) AS Data,

    LEN(data) AS Level,

    SUBSTRING(data, LEN(data), 1) AS Character

    FROM @t

    UNION ALL

    SELECTcte.ID,

    SUBSTRING(t.data,0, LEN(cte.data)) AS Data,

    LEN(cte.data) - 1 AS Level,

    --'' AS Character

    SUBSTRING(cte.data, LEN(cte.data)-1, 1) AS Character

    FROM @t t

    INNER JOIN cte ON t.ID = cte.ID

    AND cte.Level > 0

    )

    select ID, COUNT(*) [No Of @]

    from cte

    WHERE Character like '@'

    GROUP BY ID

    ORDER BY ID

    let us know if it helps you.

    Tejas Shah

    http://www.SQLYoga.com

    Tejas Shah

  • September 9, 2009 at 10:14 pm

    #1051552

    Tejas Shah (9/9/2009)

    Hi,

    You can achieve the same by this query.

    DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(100))

    INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;tejas@SQLY@oga.com'

    INSERT INTO @t(data) SELECT 'tejas@tejas.com;tejas@shah.com;'

    ;WITH CTE AS(

    SELECT ID,

    SUBSTRING(data,0, LEN(data)+1) AS Data,

    LEN(data) AS Level,

    SUBSTRING(data, LEN(data), 1) AS Character

    FROM @t

    UNION ALL

    SELECTcte.ID,

    SUBSTRING(t.data,0, LEN(cte.data)) AS Data,

    LEN(cte.data) - 1 AS Level,

    --'' AS Character

    SUBSTRING(cte.data, LEN(cte.data)-1, 1) AS Character

    FROM @t t

    INNER JOIN cte ON t.ID = cte.ID

    AND cte.Level > 0

    )

    select ID, COUNT(*) [No Of @]

    from cte

    WHERE Character like '@'

    GROUP BY ID

    ORDER BY ID

    let us know if it helps you.

    Tejas Shah

    http://www.SQLYoga.com

    I'd recommend NOT using a recursive CTE for anything except for (possibly) hierarchies because they are as slow as a While Loop. Take a look at the other solutions which require neither and are much shorter and performant.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 9, 2009 at 11:06 pm

    #1051564

    didn't think about this

    SELECT LEN(@String)-LEN(REPLACE(@String,'@','')) 🙂

    "Keep Trying"

  • September 9, 2009 at 11:34 pm

    #1051571

    neo_pom (9/9/2009)

    If a column keeps email address look like these

    p@hotmail.com;a@hotmail.com

    I'd like count '@' in the column

    How can I write TSQL just only one statement(without loop) to do that ?

    Hi,

    Suppose the mailId stored in the text data type column, then first remove all the empty space,

    and then finding the repeating characters.

    declare @abc table (mails text)

    insert into @abc (mails)

    select '1@123.com ; 12@123.com'

    union all

    select ' 123@123.com ; 1234@123.com '

    update @abc

    set mails = (replace(cast(mails as char(8000)),' ',''))

    select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc

    RESULT

    2

    2

    declare @abc table (mails text)

    insert into @abc (mails)

    select '1@123.com ; 12@123.com'

    union all

    select ' 123@123.com ; 1234@123.com '

    --update @abc

    --set mails = (replace(cast(mails as char(8000)),' ',''))

    select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc

    RESULT

    2

    3

  • September 22, 2009 at 12:52 pm

    #1057062

    So darn clever!

    Thanks

  • October 10, 2009 at 10:18 am

    #1064472

    arun.sas (9/9/2009)

    neo_pom (9/9/2009)

    If a column keeps email address look like these

    p@hotmail.com;a@hotmail.com

    I'd like count '@' in the column

    How can I write TSQL just only one statement(without loop) to do that ?

    Hi,

    Suppose the mailId stored in the text data type column, then first remove all the empty space,

    and then finding the repeating characters.

    declare @abc table (mails text)

    insert into @abc (mails)

    select '1@123.com ; 12@123.com'

    union all

    select ' 123@123.com ; 1234@123.com '

    update @abc

    set mails = (replace(cast(mails as char(8000)),' ',''))

    select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc

    RESULT

    2

    2

    declare @abc table (mails text)

    insert into @abc (mails)

    select '1@123.com ; 12@123.com'

    union all

    select ' 123@123.com ; 1234@123.com '

    --update @abc

    --set mails = (replace(cast(mails as char(8000)),' ',''))

    select (Datalength(mails) - Datalength(REPLACE(cast(mails as char(8000)), '@', ''))) from @abc

    RESULT

    2

    3

    Ummm... nope... there's only 2 "@" in both lines.

    Simple mod does make it work, though.

    declare @abc table (mails text)

    insert into @abc (mails)

    select '1@123.com ; 12@123.com'

    union all

    select ' 123@123.com ; 1234@123.com '

    --update @abc

    --set mails = (replace(cast(mails as char(8000)),' ',''))

    select (Datalength(cast(mails as VARchar(8000))) - Datalength(REPLACE(cast(mails as VARchar(8000)), '@', ''))) from @abc

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy