Re: select LEFT
From: Tamas Bojcan - bojci (bojcan_at_freemail.hu)
Date: 08/21/04
- Next message: Pankaj Agarwal [MSFT]: "RE: Query taking long time to execute"
- Previous message: Pankaj Agarwal [MSFT]: "RE: SQL Error Log"
- In reply to: Itzik Ben-Gan: "Re: select LEFT"
- Next in thread: Darren Woodbrey: "Re: select LEFT"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 22 Aug 2004 00:00:41 +0200
Hi,
Oohh, what I do see! Thanks for correcting my stupidity!!! Of course MAX
can't be used as I wrote...
"Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OvZtJA7hEHA.4092@TK2MSFTNGP10.phx.gbl...
> MAX in SQL/T-SQL is supported only as an aggregate function accepting a
> single expression as an input. MAX doesn't support multiple arguments.
>
> How you deal with the case where there is no '/' in the input depends on
the
> requirement.
>
> If you want to get all the string:
>
> LEFT(@s, CHARINDEX('/',@s+'/')-1)
>
> If you want to get an empty string:
>
> LEFT(@s, PATINDEX('%_/%',@s))
>
> If you want to get a NULL:
>
> LEFT(@s, NULLIF(CHARINDEX('/',@s), 0)-1)
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Tamas Bojcan - bojci" <bojcan@freemail.hu> wrote in message
> news:OovDWs2hEHA.1972@TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I wrote the same as you but you used CASE statement to handle that case
> when
> > '/' has zero occurance while I used the MAX.
> >
> > In details:
> > ( CHARINDEX( '/' ,> 'left/right' )-1) < 0 when there is no '/'
> character
> > in the string so the MAX gives back 0.
> >
> >
> >
> > "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
> > news:uN7tid1hEHA.1184@TK2MSFTNGP12.phx.gbl...
> > > >>select SUBSTRING( 'left/right', 1, MAX( CHARINDEX( '/' ,
> > > 'left/right' )-1,0 ) )
> > >
> > > What does the MAX function does there?
> > > Also what happens if '/' has zero ocurances in the string?
> > >
> > > Try
> > >
> > > DECLARE @v VARCHAR(50)
> > > SET @v = 'left/right'
> > > SELECT LEFT(@v, CASE WHEN CHARINDEX('/',@v) > 0 THEN
> CHARINDEX('/',@v) -1
> > > ELSE 0 END )
> > >
> > >
> > > --
> > > Roji. P. Thomas
> > > Net Asset Management
> > > https://www.netassetmanagement.com
> > >
> > >
> > > "Tamas Bojcan - bojci" <bojcan@freemail.hu> wrote in message
> > > news:eESlIqvhEHA.1644@tk2msftngp13.phx.gbl...
> > > > Sorry, I was wrong I forgot to decrease the CHARINDEX value...
> > > >
> > > > select SUBSTRING( 'left/right', 1,CHARINDEX( '/' ,
'left/right' )-1 )
> > > >
> > > > or if you are not sure that the string contains the character...:
> > > >
> > > > select SUBSTRING( 'left/right', 1, MAX( CHARINDEX( '/' ,
> > 'left/right' )-1,
> > > > 0 ) )
> > > >
> > > > "Tamas Bojcan - bojci" <bojcan@freemail.hu> wrote in message
> > > > news:er6svjvhEHA.384@TK2MSFTNGP10.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > Use SUBSTRING and CHARINDEX. For example: select SUBSTRING(
> > > 'left/right',
> > > > 1,
> > > > > CHARINDEX( '/' , 'left/right' ) )
> > > > >
> > > > >
> > > > > "Darren Woodbrey" <darrenwoodbrey@hpfairfield.com> wrote in
message
> > > > > news:elrBogvhEHA.712@TK2MSFTNGP09.phx.gbl...
> > > > > > How can I select all character to the left of a certain
character.
> > I
> > > > need
> > > > > > to select all characters to the left of a "/". LEFT will not
work
> > > > because
> > > > > > the amount of characters will be different in each case.
Thanks!
> > > > > >
> > > > > > Darren
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Pankaj Agarwal [MSFT]: "RE: Query taking long time to execute"
- Previous message: Pankaj Agarwal [MSFT]: "RE: SQL Error Log"
- In reply to: Itzik Ben-Gan: "Re: select LEFT"
- Next in thread: Darren Woodbrey: "Re: select LEFT"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
