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

Posted on 2011-05-03
Medium Priority
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

Pratima Pharande earned 668 total points
ID: 35511480
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
Kelvin Sparks earned 664 total points
ID: 35511518
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
Imran Javed Zia earned 668 total points
ID: 35511528
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

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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