Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
misterraj
Yak Posting Veteran
94 Posts |
 Posted - 2009-01-09 : 06:16:04
|
| i have two fields first name and second name in a table. i need a query which will give me the results like this.for eg: Muthu kumar would be kumar, M girish Mathur would be Mathur, G randeep Hooda would be Hooda, RSivaji rao Kaur would be Rao, S (incase rao kaur is the last name)Sivaji rao Kaur would be Kaur, S (incase Sivaji Rao woule be I Name)on the essence it would be lastname until first space followed by a command and space and first letter of first namecan i use a single query which i would get the result |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-09 : 06:19:13
|
| select substring(secondname,1,case when charindex(' ',secondname,1) = 0 then len(secondname) else charindex(' ',secondname,1)-1 end )+','+upper(left(firstname,1)) from @tableJai Krishna |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-09 : 06:22:11
|
| [code]select lname+', '+UPPER(LEFT(fname,1)) FROM table[/code] |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-01-09 : 06:22:52
|
| declare @str varchar(32),@str1 varchar(32)select @str= 'sivaji', @str1='rao kaur 'select substring(@str1,1,charindex(' ',@str1,1))+','+left(@str,1) |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2009-01-09 : 06:31:14
|
| i need them in one single statement, one single query |
|
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-09 : 06:32:35
|
quote:
Originally posted by misterraj
i need them in one single statement, one single query
Try this select substring(secondname,1,case when charindex(' ',secondname,1) = 0 then len(secondname) else charindex(' ',secondname,1)-1 end )+','+upper(left(firstname,1)) from @tableJai Krishna |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2009-01-09 : 06:34:41
|
| thanks jai krishna it worked on the first shot!! AAA rating to you |
|
|
|
|
|
|
|
|
first name - lastname