query - about space - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query - about space

Author

Topic

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2009-03-31 : 11:23:37

i have groupname - column values given below: i want just first name before first space.

Apr2008v2n3 4/29/2008 2:59:20 PM
August2008v2n7 8/28/2008 10:27:09 AM
copd_update_Jan2008 1/22/2008 3:33:43 PM
copd_update_n1v1 8/8/2007 10:39:35 AM
copd_update_Nov2007 11/28/2007 2:42:20 PM
copd_update_Oct2007 10/24/2007 10:47:26 AM
copd_update_sept2007 9/26/2007 11:20:29 AM

i want like:

Apr2008v2n3
August2008v2n7
copd_update_Jan2008
copd_update_n1v1
copd_update_Nov2007
copd_update_Oct2007
copd_update_sept2007

how can i get that?

i tried in my query:

select groupid, substring(groupname,1,'') from groups

but i m getting an eror - Argument data type varchar is invalid for argument 3 of substring function.

any help? thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 11:32:53

[code]declare @str varchar(1000)
set @str='Apr2008v2n3 4/29/2008 2:59:20 PM'
select left(@str,charindex(' ',@str)-1)[/code]

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2009-03-31 : 11:40:14

thank you so much!

i got the correct results.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 11:41:11

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-01 : 00:16:20

quote:
Originally posted by sqlhelp14

i have groupname - column values given below: i want just first name before first space.

Apr2008v2n3 4/29/2008 2:59:20 PM
August2008v2n7 8/28/2008 10:27:09 AM
copd_update_Jan2008 1/22/2008 3:33:43 PM
copd_update_n1v1 8/8/2007 10:39:35 AM
copd_update_Nov2007 11/28/2007 2:42:20 PM
copd_update_Oct2007 10/24/2007 10:47:26 AM
copd_update_sept2007 9/26/2007 11:20:29 AM

i want like:

Apr2008v2n3
August2008v2n7
copd_update_Jan2008
copd_update_n1v1
copd_update_Nov2007
copd_update_Oct2007
copd_update_sept2007

how can i get that?

i tried in my query:

select groupid, substring(groupname,1~~,''~~,charindex(' ',@str)-1) from groups

but i m getting an eror - Argument data type varchar is invalid for argument 3 of substring function.

any help? thanks

u have to specify the length of the string in the 3 parameter in substring funciton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources