Post reply

Create View with Split Columns

  • March 16, 2009 at 10:48 am

    #127156

    Task:

    Create view of a table that splits one column value into two column values. The single column value has two spaces that can be used as the delimiter.

    I have a table called Table 1. Table 1 has 2 columns in it. Column 1 is ptid, Column 2 is im_map. im_map is a varchar(20) datatype and contains values such as '000123456 001234567'

    I would like to create a view on this table whereby the view contains 4 columns. The third and fourth column names will be 'xcoord' and 'ycoord' respectively. Hence during the view creation I will have to be able split the im_map column into two columns. My current code is something like this, however I do not want to hard code the im_map values.

    /* Example */

    DECLARE @mapcoords nvarchar(255)

    DECLARE @pos int

    set @mapcoords = '2432423234 9809800' /* Note: This value will not be hard coded and needs to come from the values in the im_map field...*/

    set @pos = CHARINDEX(' ', @mapcoords)

    select LTRIM(RTRIM(substring(@mapcoords,1,CHARINDEX(' ', @mapcoords)-1))) As X_Coord, LTRIM(RTRIM(substring(@mapcoords,CHARINDEX(' ', @mapcoords),LEN(@mapcoords)-1))) As Y_Coord

    Any help would be greatly appreciated....

  • March 16, 2009 at 11:02 am

    #960423

    Hi

    Here a little sample:

    DECLARE @t TABLE (c1 VARCHAR(100), c2 VARCHAR(100))

    INSERT INTO @t VALUES ('00000 11111', '22222 33333')

    SELECT SUBSTRING(c1, 1, CHARINDEX(' ', c1)),

    SUBSTRING(c1, CHARINDEX(' ', c1) + 1, 100),

    SUBSTRING(c2, 1, CHARINDEX(' ', c2)),

    SUBSTRING(c2, CHARINDEX(' ', c2) + 1, 100)

    FROM @t

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • March 16, 2009 at 11:16 am

    #960436

    Thanks for your prompt replay however, I am not sure I understand what you have done.

    Lets say I have a table with a field in it that has a value '00123456 01234567'. The field is called 'map_coords'.

    When I generate a view for this table I want my view to contain the original 'map_coords' value '00123456 01234567' as well as two new fields called 'xcoord' and 'ycoord'

    The value that would be in the 'xcoord' would be '00123456' and the value in the 'ycoord' field would be '01234567'

    When I ran your code I dod not see this? Was I miss understanding?

  • March 16, 2009 at 11:24 am

    #960448

    Hi

    It was just an example with test values. Try this:

    DECLARE @t TABLE (map_coords VARCHAR(100))

    INSERT INTO @t VALUES ('00123456 01234567')

    SELECT map_coords,

    SUBSTRING(map_coords, 1, CHARINDEX(' ', map_coords)) xcoord,

    SUBSTRING(map_coords, CHARINDEX(' ', map_coords) + 1, 100) ycoord

    FROM @t

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • March 16, 2009 at 11:32 am

    #960459

    Much better! Thank you very much for the help! Have a great day!

  • March 16, 2009 at 11:35 am

    #960465

    This page intentionally left blank.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 16, 2009 at 12:26 pm

    #960514

    Yes and thank you for the feedback as well....

  • March 16, 2009 at 1:32 pm

    #960545

    Hal, my apologies.

    I thought perhaps there was a misunderstanding about the question. When I saw that Flo had answered to your satisfaction I thought I deleted my previous post.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 16, 2009 at 11:20 pm

    #960795

    Bob Hovious (3/16/2009)

    Hal, my apologies.

    I thought perhaps there was a misunderstanding about the question. When I saw that Flo had answered to your satisfaction I thought I deleted my previous post.

    DELETE was disabled on the forums about a week ago.

    --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)
    Intro to Tally Tables and Functions

  • March 17, 2009 at 6:36 am

    #960958

    Thanks, Jeff.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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