Post reply

Trimming data from cells.

  • January 30, 2012 at 4:53 pm

    #252129

    Hi Guys,

    I've got a bit of an issue, I need to trim some data from the end of a few cells(3000+, okay not a few).

    The good thing is though the data is primarily in the same style/format in each cell so that'll make it a bit easier.

    Cell data looks like the below, I need to be able to trim off the comma and numbers at the end and leave the email intact.

    Blah@blah.blah,0011

    Lol@lol.lol,0078

    Rofl@rofl.rofl,0023

    Not entirely sure how I should go about doing this?

    Cheers

  • January 30, 2012 at 6:00 pm

    #1440309

    How about this?

    DECLARE @Table TABLE ( EmailID VARCHAR(100) )

    INSERT INTO @Table (EmailID)

    SELECT 'Blah@blah.blah,0011'

    UNION ALL SELECT 'Lol@lol.lol,0078'

    UNION ALL SELECT 'Rofl@rofl.rofl,0023'

    SELECT REVERSE( STUFF(CrsApp.EmailID,1, CHARINDEX(',' ,CrsApp.EmailID),'' )) NewCol

    FROM @Table A

    CROSS APPLY (SELECT REVERSE(A.EmailID) EmailID ) CrsApp

    /*

    Result:

    --------

    Blah@blah.blah

    Lol@lol.lol

    Rofl@rofl.rofl

    */

  • January 31, 2012 at 4:38 am

    #1440503

    subzzz_ (1/30/2012)

    Hi Guys,

    I've got a bit of an issue, I need to trim some data from the end of a few cells(3000+, okay not a few).

    The good thing is though the data is primarily in the same style/format in each cell so that'll make it a bit easier.

    Cell data looks like the below, I need to be able to trim off the comma and numbers at the end and leave the email intact.

    Blah@blah.blah,0011

    Lol@lol.lol,0078

    Rofl@rofl.rofl,0023

    Not entirely sure how I should go about doing this?

    Cheers

    create table #SQLSERVER(Email varchar(20))

    insert into #SQLSERVER(email) values('Blah@blah.blah,0011')

    select substring(email,1,CHARINDEX(',', email) - 1) from #SQLSERVER

    drop table #SQLSERVER

  • February 1, 2012 at 9:12 am

    #1441270

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • February 1, 2012 at 9:47 am

    #1441303

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    I see what you are asking but as far as "There is no concept of a "Cell" in SQL Server...

    What about in SQL Reporting Services?

  • February 1, 2012 at 9:57 am

    #1441309

    tyson.price (2/1/2012)

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    I see what you are asking but as far as "There is no concept of a "Cell" in SQL Server...

    What about in SQL Reporting Services?

    SQL Server Reporting Services is another group of discussion forums. It's not clear if these comma separated values need to be removed from a database table, or if this is some type of reporting issue.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • February 1, 2012 at 4:29 pm

    #1441490

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    Sorry, my mistake.

    It's a force of habit from talking to myself, as I work alone and am self taught in SQL/Excel. I always called it a cell even though it's incorrect.

    I'm just looking to working with the entire column data.

    I will try the above script sometime today, end of period reporting has got me overloaded at the moment 🙁

  • February 1, 2012 at 5:14 pm

    #1441497

    subzzz_ (2/1/2012)

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    Sorry, my mistake.

    It's a force of habit from talking to myself, as I work alone and am self taught in SQL/Excel. I always called it a cell even though it's incorrect.

    I'm just looking to working with the entire column data.

    I will try the above script sometime today, end of period reporting has got me overloaded at the moment 🙁

    I know how that is. This evening I'm trying to get Jan 2012 reporting out the door too. Of course there are a couple of other interesting puzzles that always seem to crop up at month end. 🙂

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • February 1, 2012 at 9:20 pm

    #1441527

    Eric M Russell (2/1/2012)

    tyson.price (2/1/2012)

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    I see what you are asking but as far as "There is no concept of a "Cell" in SQL Server...

    What about in SQL Reporting Services?

    SQL Server Reporting Services is another group of discussion forums. It's not clear if these comma separated values need to be removed from a database table, or if this is some type of reporting issue.

    Although I agree, "Cell" is certainly a convenient way of saying such a thing that I believe everyone would understand. What's your alternative? Field? Even though BOL says...

    Each row represents a unique record, and each column represents a [font="Arial Black"]field [/font]within the record.

    ...that doesn't sit with me too well either. Of course, equating a "row" to a "record" also gets me twitchin'. 😛

    Anyone that would care to answer, please. What do YOU call the value sitting at the intersection of a given row and column in SQL Server?

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

  • February 2, 2012 at 4:09 am

    #1441653

    Eric M Russell (2/1/2012)

    subzzz_ (2/1/2012)

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    Sorry, my mistake.

    It's a force of habit from talking to myself, as I work alone and am self taught in SQL/Excel. I always called it a cell even though it's incorrect.

    I'm just looking to working with the entire column data.

    I will try the above script sometime today, end of period reporting has got me overloaded at the moment 🙁

    I know how that is. This evening I'm trying to get Jan 2012 reporting out the door too. Of course there are a couple of other interesting puzzles that always seem to crop up at month end. 🙂

    Yep...and this is a leap year so February reporting may have bugs that have been sleeping for four years and will pop up :w00t:

  • February 2, 2012 at 4:47 am

    #1441676

    Decided not to go there...

  • February 2, 2012 at 7:36 am

    #1441829

    Jeff Moden (2/1/2012)

    Eric M Russell (2/1/2012)

    tyson.price (2/1/2012)

    Eric M Russell (2/1/2012)

    There is no concept of a "cell" in SQL Server. Are you talking about SQL Server or Excel?

    I see what you are asking but as far as "There is no concept of a "Cell" in SQL Server...

    What about in SQL Reporting Services?

    SQL Server Reporting Services is another group of discussion forums. It's not clear if these comma separated values need to be removed from a database table, or if this is some type of reporting issue.

    Although I agree, "Cell" is certainly a convenient way of saying such a thing that I believe everyone would understand. What's your alternative? Field? Even though BOL says...

    Each row represents a unique record, and each column represents a [font="Arial Black"]field [/font]within the record.

    ...that doesn't sit with me too well either. Of course, equating a "row" to a "record" also gets me twitchin'. 😛

    Anyone that would care to answer, please. What do YOU call the value sitting at the intersection of a given row and column in SQL Server?

    Generally I'd call it a "value" or a "set of values". However, since the values contained in this column are composed of multiple delimited sub-values, I think it could be described as a "tuple".

    I'll defer to Joe Celko. 🙂

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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