creating a column dynamically using t sql in sql server 2005 management studio

Posted on 2011-05-03
Last Modified: 2012-06-27
I have a query that returns the following result set
Name                          location         
UKCWOV1234567      germany         
UKCWOV1234568      england         
UKCWOV1234569      spain         
UKCWOT1234569      spain         
UKCWOS1234569      france       

I would like to modify the query so that it checks the 5th and 6th characters of
the Name and if the 5th and 6th characters =OV then create and populate the column is_OV
Name                        location             is_OV         
UKCWOV1234567      germany          yes         
UKCWOV1234568      england           yes         
UKCWOV1234569      spain            yes         
UKCWOT1234569      spain           no         
UKCWOS1234569      france            no       

thanks in advance for any advice
Question by:blossompark
    LVL 39

    Accepted Solution

    First add col like this

    ALTER TABLE table_name
    ADD is_OV varchar(5)

    Then insert the data like this

    Update table_name

    Set is_OV = Case When substring(Name,5,2) = 'OV' then 'yes' else 'no' End
    LVL 22

    Assisted Solution

    by:Kelvin Sparks
    In a query it would be

    SELECT [Name], Location, CASE WHEN SUBSTRING([Name],5,2) = 'OV' THEN 'yes' ELSE 'no' END AS _OV FROM Table_Name
    LVL 16

    Assisted Solution

    by:Imran Javed Zia
    you can use one of following queries

    Name, location,  
    (Case When substring(Name,5,2) = 'OV' then 'yes' else 'no' End) as is_OV
    From YourTable

    Name, location,  
    (Case When substring(Name,5,2) = 'OV' then 1 else 0 End) as is_OV
    From YourTable


    Author Closing Comment

    Hi pratima mcs, kelvinsparks and ijz,,,,
    thanks for all your prompt responses...all work!! so many choices :-)
    thanks again ...greatly appreciated

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    spx for moving values to new table 5 46
    Format Number Field 10 34
    SQL for monthly balance change 15 51
    Grouping within groups: SQL 5 29
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now