|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Replace IfI have a small list of names. I want to create a table with two columns
using this list. The first would be the names as per the list. The second would replace all the males names with 'Male', and the female names with 'Female'. I'm able to run a statement with a single REPLACE command, SELECT REPLACE(tblName,'Scott','Male'), ... but struggle to replace any of the other names. I guess I'm looking for some sort of replace if command. Please help. Thanks, SHC You'd really need to provide DDL + INSERT statements of your sample data +
expected results. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. <shallcoo***@afig.com.au> wrote in message news:1152836503.254417.157530@s13g2000cwa.googlegroups.com... I have a small list of names. I want to create a table with two columnsusing this list. The first would be the names as per the list. The second would replace all the males names with 'Male', and the female names with 'Female'. I'm able to run a statement with a single REPLACE command, SELECT REPLACE(tblName,'Scott','Male'), ... but struggle to replace any of the other names. I guess I'm looking for some sort of replace if command. Please help. Thanks, SHC shallcoo***@afig.com.au wrote:
> I have a small list of names. I want to create a table with two columns I think you would be better off creating a table with a distinct list> using this list. The first would be the names as per the list. The > second would replace all the males names with 'Male', and the female > names with 'Female'. > > I'm able to run a statement with a single REPLACE command, > > SELECT REPLACE(tblName,'Scott','Male'), ... > > but struggle to replace any of the other names. I guess I'm looking for > some sort of replace if command. of all your names and sex, and then using this table to set the Male/Female flag in your main table. ie. CREATE TABLE names( name VARCHAR(50) NOT NULL, -- Primary key sex CHAR(1) NOT NULL ) INSERT names VALUES('John', 'M') INSERT names VALUES('Peter', 'M') INSERT names VALUES('Jane, 'F') ....etc UPDATE YourTable SET sex = (SELECT CASE WHEN n.sex = 'M' then 'Male' ELSE 'Female' END FROM names n WHERE n.name = YourTable.name) Thanks Chris.
My rights to the source data is limited to read only. Therefore my solution needs to be a query output only. SHC Chris Lim wrote: Show quote > shallcoo***@afig.com.au wrote: > > I have a small list of names. I want to create a table with two columns > > using this list. The first would be the names as per the list. The > > second would replace all the males names with 'Male', and the female > > names with 'Female'. > > > > I'm able to run a statement with a single REPLACE command, > > > > SELECT REPLACE(tblName,'Scott','Male'), ... > > > > but struggle to replace any of the other names. I guess I'm looking for > > some sort of replace if command. > > I think you would be better off creating a table with a distinct list > of all your names and sex, and then using this table to set the > Male/Female flag in your main table. > > ie. > > CREATE TABLE names( > name VARCHAR(50) NOT NULL, -- Primary key > sex CHAR(1) NOT NULL > ) > > INSERT names VALUES('John', 'M') > INSERT names VALUES('Peter', 'M') > INSERT names VALUES('Jane, 'F') > ...etc > > UPDATE YourTable > SET sex = (SELECT CASE WHEN n.sex = 'M' then 'Male' ELSE > 'Female' END > FROM names n > WHERE n.name = YourTable.name) shallcoo***@afig.com.au wrote:
> Thanks Chris. Are you able to create a temp table, or use a table variable? Or are> > My rights to the source data is limited to read only. Therefore my > solution needs to be a query output only. you limited to only SELECT statements? Only to SELECT I'm afraid.
Chris Lim wrote: Show quote > shallcoo***@afig.com.au wrote: > > Thanks Chris. > > > > My rights to the source data is limited to read only. Therefore my > > solution needs to be a query output only. > > Are you able to create a temp table, or use a table variable? Or are > you limited to only SELECT statements? Again, please give us DDL + INSERT statements of sample data + expected
results and we can put something together fairly quickly. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. <shallcoo***@afig.com.au> wrote in message news:1152841108.805205.233580@h48g2000cwc.googlegroups.com... Only to SELECT I'm afraid.Chris Lim wrote: Show quote > shallcoo***@afig.com.au wrote: > > Thanks Chris. > > > > My rights to the source data is limited to read only. Therefore my > > solution needs to be a query output only. > > Are you able to create a temp table, or use a table variable? Or are > you limited to only SELECT statements? What do you mean by DDL + INSERTS statments?
My output would look like, Name Title Age Gender ----------------------------------------------------- Scott Mr 28 Male Paul Dr 36 Male Claire Mrs 42 Female Mike Mr 25 Male Louise Ms 31 Female With Name, Title and Age coming straight from my source data, whilst Gender is derived from Name using some sort of select replace if statement. Thanks Tom Moreau wrote: Show quote > Again, please give us DDL + INSERT statements of sample data + expected > results and we can put something together fairly quickly. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Toronto, ON Canada > . > <shallcoo***@afig.com.au> wrote in message > news:1152841108.805205.233580@h48g2000cwc.googlegroups.com... > Only to SELECT I'm afraid. > > Chris Lim wrote: > > shallcoo***@afig.com.au wrote: > > > Thanks Chris. > > > > > > My rights to the source data is limited to read only. Therefore my > > > solution needs to be a query output only. > > > > Are you able to create a temp table, or use a table variable? Or are > > you limited to only SELECT statements? shallcoo***@afig.com.au wrote:
> What do you mean by DDL + INSERTS statments? Basically providing CREATE TABLE/COSNTRAINT/INDEX statements, plusINSERTs to create sample data. See http://www.aspfaq.com/etiquette.asp?id=5006 Cheers, Chris DDL = Data Definition Language, i.e. CREATE TABLE statements, etc. An
INSERT statement is used to insert data into the table. If we had those, we could just cut and paste, and then solve the problem quickly. You would need a SELECT with a long CASE in it to derive the gender from the name. Otherwise, you could have another table with Name-Gender mappings and do: SELECT n.* , m.Gender from Names n join Mappings m on m.Name = n.Name BTW, what would you do with someone named Pat, Hilary, Bobby, Ronnie or Fran? -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. <shallcoo***@afig.com.au> wrote in message news:1152841929.703734.120660@75g2000cwc.googlegroups.com... What do you mean by DDL + INSERTS statments?My output would look like, Name Title Age Gender ----------------------------------------------------- Scott Mr 28 Male Paul Dr 36 Male Claire Mrs 42 Female Mike Mr 25 Male Louise Ms 31 Female With Name, Title and Age coming straight from my source data, whilst Gender is derived from Name using some sort of select replace if statement. Thanks Tom Moreau wrote: Show quote > Again, please give us DDL + INSERT statements of sample data + expected > results and we can put something together fairly quickly. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Toronto, ON Canada > . > <shallcoo***@afig.com.au> wrote in message > news:1152841108.805205.233580@h48g2000cwc.googlegroups.com... > Only to SELECT I'm afraid. > > Chris Lim wrote: > > shallcoo***@afig.com.au wrote: > > > Thanks Chris. > > > > > > My rights to the source data is limited to read only. Therefore my > > > solution needs to be a query output only. > > > > Are you able to create a temp table, or use a table variable? Or are > > you limited to only SELECT statements? shallcoo***@afig.com.au wrote:
> Only to SELECT I'm afraid. In that case you will have a very big CASE expression!e.g. SELECT name, sex = CASE WHEN name in ('John', 'James', 'David') then 'Male' WHEN name in ('Jane', 'Sally', 'Betty') then 'Female' ELSE 'Unknown' END FROM YourTable If you name column contains both first and last names, then that'll be even worse.... Chris Will it matter that I'm using Microsoft Query? I'm getting the message
"Didn't expect 'name' in the SELECT column list" SHC Chris Lim wrote: Show quote > shallcoo***@afig.com.au wrote: > > Only to SELECT I'm afraid. > > In that case you will have a very big CASE expression! > > e.g. SELECT name, > sex = CASE WHEN name in ('John', 'James', 'David') > then 'Male' > WHEN name in ('Jane', 'Sally', > 'Betty') then 'Female' > ELSE 'Unknown' > END > FROM YourTable > > If you name column contains both first and last names, then that'll be > even worse.... > > Chris shallcoo***@afig.com.au wrote:
> Will it matter that I'm using Microsoft Query? I'm getting the message I'm not familiar with Microsoft Query, but if it's just a client tool> "Didn't expect 'name' in the SELECT column list" that connects to a SQL Server database then the query should be fine, assuming the column name in your table actually is 'name'. Chris OK, I understand. Your right to assume I've simplified the nature of my
data and used a scenario. Thanks for all your help. SHC Chris Lim wrote: Show quote > shallcoo***@afig.com.au wrote: > > Will it matter that I'm using Microsoft Query? I'm getting the message > > "Didn't expect 'name' in the SELECT column list" > > I'm not familiar with Microsoft Query, but if it's just a client tool > that connects to a SQL Server database then the query should be fine, > assuming the column name in your table actually is 'name'. > > Chris  
Other interesting topics
|
|||||||||||||||||||||||
