Case statemant- separate forenames

Hi All

I need to separate the forenames within the staff table, at present it looks something like this:

John-Test
John - Test
John - Test
Richard
Bill Jones
John - Test MiddleName
John-Test Mid

I need to split the middlenames out and remove the spaces within the double barrelled names

John-Test
John-Test
John-Test
Richard
Bill
John-Test
John-Test

Thanks in advanced

Rich

hi...

there is a recent post solving a similar issue..have a look...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073376&SiteID=1

|||

It will do..

Create Table Names
(
FullName varchar(100)
)
Go

Insert Into Names values ('John-Test ')
Insert Into Names values ('John - Test')
Insert Into Names values ('John - Test')
Insert Into Names values ('Richard')
Insert Into Names values ('Bill Jones')
Insert Into Names values ('John - Test MiddleName')
Insert Into Names values ('John-Test Mid')

Go

--Simple / Direct Logic

Select
A Actual
,B + Substring(C,1,CharIndex(' ',C)) Result
From
(
Select
FullName A
,Replace(Substring(FullName,1,CharIndex('-',FullName)),' ','') B
,Ltrim(Substring(FullName,CharIndex('-',FullName)+1,len(FullName)-CharIndex('-',FullName))) + ' ' C
from
Names
) as Data

Go

--Creating Function
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProperName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetProperName]
Go

Create Function Dbo.GetProperName(@.Name as varchar(100)) Returns Varchar(100)
as
Begin
Declare @.B as Varchar(100);
Declare @.C as Varchar(100);
Declare @.Result as Varchar(100);

Select

@.B = Replace(Substring(@.Name,1,CharIndex('-',@.Name)),' ','')
,@.C = Ltrim(Substring(@.Name,CharIndex('-',@.Name)+1,len(@.Name)-CharIndex('-',@.Name))) + ' '

Select @.Result = @.B + Substring(@.C,1,CharIndex(' ',@.C))
return @.Result;
End

go
Select FullName, Dbo.GetProperName(fullname) from names
go
Drop Table Names

CASE problems with select statement.

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.
I want to somehow CASE the WHERE clause based on a parameter of let's
say, @.DateType
SELECT *
FROM <table>
WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
SELECT *
FROM <table>
WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
I know there is something simple i'm over looking.
thanks folks...
-fd
Are you talking about something like this? Note this sample works in the
AdventureWorks sample database:
DECLARE @.start DATETIME;
DECLARE @.end DATETIME;
DECLARE @.dateType VARCHAR(5);
SELECT @.start = '2001-08-01',
@.end = '2001-08-15',
@.dateType = 'Ship';
SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @.start AND @.end;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
> DECLARE @.start DATETIME;
> DECLARE @.end DATETIME;
> DECLARE @.dateType VARCHAR(5);
> SELECT @.start = '2001-08-01',
> X @.end = '2001-08-15',
> X @.dateType = 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> X WHEN 'Due' THEN DueDate
> X WHEN 'Ship' THEN ShipDate
> X END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
-- Add the parameters for the stored procedure here
@.BeginDate datetime,
@.EndDate datetime,
@.DateType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Permits
WHERE CASE @.dateType
WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
END
END
the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.
thanks for your time...
|||On Mar 6, 11:01Xpm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23Xpm, "Mike C#" <x...@.xyz.com> wrote:
>
>
>
>
>
>
>
>
>
> sorry, i should have added a little more info. Xi use this in a stored
> procedure and
> i have the following thus far.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
> (
> X X X X -- Add the parameters for the stored procedure here
> X X X X @.BeginDate datetime,
> X X X X @.EndDate datetime,
> X X X X @.DateType varchar(50)
> )
> AS
> BEGIN
> X X X X SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> X WHEN 'S_DATE' THEN 'S_DATE >= @.BeginDate AND S_DATE <= @.EndDate'
> X WHEN 'G_DATE' THEN 'G_DATE >= @.BeginDate AND G_DATE <= @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. Xthe columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd
|||There is case, But there is also Between
Select * from table where DateColumn Between @.BeginDate and @.enddate
Case should not be used in a where.
Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.
!0 fold performance increase.
-R
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
> parameter value.
> I want to somehow CASE the WHERE clause based on a parameter of let's
> say, @.DateType
> SELECT *
> FROM <table>
> WHERE <columnA> >= @.BeginDate AND <columnA> <= @.EndDate
> SELECT *
> FROM <table>
> WHERE <columnB> >= @.BeginDate AND <columnB> <= @.EndDate
> I know there is something simple i'm over looking.
> thanks folks...
> -fd
|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> There is case, But there is also Between
> Select * from table where DateColumn Between @.BeginDate and @.enddate
> Case should not be used in a where.
> Better to create a Table Variable and load from an index of Quailifing
> Primary keys.
> Then Join on the Table variable.
> !0 fold performance increase.
BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.

Cant Use Distinct here!

Hi All!!!
I'd Like to know how can I select one only row for each reference... I will post here the SQL clause
Note that I am working on Access

SELECT Badges.BadgeReference, Visitors.VisitorName, EventVisitors.VisitorCheckOutDate, EventVisitors.Event, EventVisitors.VisitorBadge, Visitors.VisitorState
FROM (Visitors INNER JOIN Badges ON Visitors.VisitorReference = Badges.BadgeReference) INNER JOIN EventVisitors ON (Visitors.Visitor_ID = EventVisitors.Visitor) AND (Badges.Badge_ID = EventVisitors.VisitorBadge)
WHERE (((Badges.BadgeReference) Is Not Null) AND ((EventVisitors.VisitorCheckOutDate) Is Not Null) AND ((Visitors.VisitorState)=0))
ORDER BY EventVisitors.VisitorCheckOutDate DESC;

It returns to me all the time the badge was out in each event... I want it to show the last event that each BadgeReference was retrieved...

Thanks in advance!SELECT Badges.BadgeReference
, Visitors.VisitorName
, EV.VisitorCheckOutDate
, EV.Event
, EV.VisitorBadge
, Visitors.VisitorState
FROM (
Visitors
INNER
JOIN Badges
ON Visitors.VisitorReference
= Badges.BadgeReference
)
INNER
JOIN EventVisitors as EV
ON Visitors.Visitor_ID = EV.Visitor
AND Badges.Badge_ID = EV.VisitorBadge
WHERE Badges.BadgeReference Is Not Null
AND EV.VisitorCheckOutDate
= ( select max(VisitorCheckOutDate)
from EventVisitors
where VisitorBadge = EV.VisitorBadge )
AND Visitors.VisitorState = 0
ORDER
BY EV.VisitorCheckOutDate DESC|||Thank you very very very very and a lots of many manys Much!!!!!

You have saved my day... You sure are pretty good at this ;)

See ya

Keep up that good work that you do :)