Without Using Substring Functions

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)

Without Using Substring Functions

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-09-04 : 01:39:17

Hi,

I will explain the scenario

There s a table name "Stud" with a column "Stud_Name" and has a value of "Thomas Peter"

I want to query out the Stud_Name as just like below

FirstName LastName
-------------------------
Thomas Peter

using a single SELECT statement WITHOUT using SUBSTRING functions

Thanks

Arv

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:42:50

looks like a homework question. when is your date of submission? why do you not want to use substring?

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-09-04 : 01:51:59

Hi,

What ever be the question ... these types of questions recently
asked for walk-ins ... I dont mind if taken as an assignment...
after all its a sort of gaining little bit knowledge..

Thanks

ARV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:02:44

use PARSENAME() function

SELECT COALESCE(PARSENAME(REPLACE(Stud_Name,' ','.'),2),
PARSENAME(REPLACE(Stud_Name,' ','.'),1)) AS FirstName,
CASE WHEN PARSENAME(REPLACE(Stud_Name,' ','.'),2) IS NULL THEN NULL ELSE PARSENAME(REPLACE(Stud_Name,' ','.'),1) 
END AS LastName
FROM Stud

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-09-04 : 02:05:53

thanks Vishakh.... Thank u very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:13:54

quote:
Originally posted by aravindt77

thanks Vishakh.... Thank u very much

You're welcome

its just that we dont want to spoonfeed you in case of homework questions

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 02:51:32

or use this logic



declare @s varchar(100)
set @s='Thomas Peter'
select 
	left(@s,charindex(' ',@s)-1) as firstname,
	right(@s,charindex(' ',reverse(@s))-1) as lastname

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:59:27

quote:
Originally posted by madhivanan

or use this logic

declare @s varchar(100)
set @s='Thomas Peter'
select
left(@s,charindex(' ',@s)-1) as firstname,
right(@s,charindex(' ',reverse(@s))-1) as lastname

Madhivanan

Failing to plan is Planning to fail

and in that case rememeber to put condition to check for existence of space character before applying left and right

select 
	left(@s,case when charindex(' ',@s)>0 then charindex(' ',@s)-1 else len(@s) end) as firstname,
	right(@s,case when charindex(' ',reverse(@s))>0 then charindex(' ',reverse(@s))-1 else 0 end) as lastname

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 05:21:26

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources