SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 3 of 3
  1. #1
    SQA Knight bklabel1's Avatar
    Join Date
    Sep 2012
    Location
    Kew Gardens, United States
    Posts
    2,596
    Post Thanks / Like
    Blog Entries
    1
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)
    Total Downloaded
    0

    SQL Select statement when you know the column number and not the name

    I was asked how to write the Select statement when you know the column number but not the name of the column.

    Select columnNumber(5) from MyTable

    Does anyone know how to do this off hand?

    Thanks,

    Kevin
    When in Florida, Don't Tampa with the code. I made this up.

  2. #2
    SQA Knight
    Join Date
    May 2006
    Location
    Playa Del Rey, California, United States
    Posts
    2,594
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0
    Off the top of my head,

    you can try 'select *', then in the scripting code just grab the 5th element or every row.
    David Lai
    SDET / Consultant
    LinkedIn profile

  3. #3
    SQA Knight bklabel1's Avatar
    Join Date
    Sep 2012
    Location
    Kew Gardens, United States
    Posts
    2,596
    Post Thanks / Like
    Blog Entries
    1
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)
    Total Downloaded
    0
    David,
    I looked it up on Google and found this answer on Stack Overflow:

    This will get you the list

    select * from information_schema.columns
    where table_name='table1' and column_name like 'a%'

    If you want to use that to construct a query, you could do something like this:

    declare @sql nvarchar(max)
    set @sql = 'select '
    select @sql = @sql + '[' + column_name +'],'
    from information_schema.columns
    where table_name='table1' and column_name like 'a%'
    set @sql = left @sql,len @sql)-1) -- remove trailing comma
    set @sql = @sql + ' from table1'
    exec sp_executesql @sql

    Note that the above is written for SQL Server.
    Thanks,

    Kevin

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 12.50%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:54 PM.

Copyright BetaSoft Inc.