Select Right From A Specific Character

13 Sep 2006 7:06 PM

Anonymous

I want to select the characters to the right of a dash and convert it to an
int so I can compare records in SQL 2000.

MDL-1553-AandB-083630
MDL-1553-AandB-498103
MDL-1553-AndB-00060

Would return either
083630
498103
00060

Or

83630
498103
60

Wasn't sure if I could convert a varchar field to int and return a number
with leading zeros. The final results need to be int because on another
piece of the code I will be writing a less than comparison.

13 Sep 2006 7:14 PM

SQL Menace

One way

right(ColumnName,(patindex('%-%',reverse(ColumnName)))-1)

example

declare @v varchar(666)
select @v ='MDL-1553-AndB-00060'

select right(@v,(patindex('%-%',reverse(@v)))-1)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Anonymous wrote:

Show quote

> I want to select the characters to the right of a dash and convert it to an
> int so I can compare records in SQL 2000.
>
> MDL-1553-AandB-083630
> MDL-1553-AandB-498103
> MDL-1553-AndB-00060
>
> Would return either
> 083630
> 498103
> 00060
>
> Or
>
> 83630
> 498103
> 60
>
> Wasn't sure if I could convert a varchar field to int and return a number
> with leading zeros. The final results need to be int because on another
> piece of the code I will be writing a less than comparison.

13 Sep 2006 7:23 PM

Anonymous

Thank you . . . that is what I was looking for.

Show quote

"SQL Menace" wrote:

> One way
>
> right(ColumnName,(patindex('%-%',reverse(ColumnName)))-1)
>
> example
>
> declare @v varchar(666)
> select @v ='MDL-1553-AndB-00060'
>
> select right(@v,(patindex('%-%',reverse(@v)))-1)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> Anonymous wrote:
> > I want to select the characters to the right of a dash and convert it to an
> > int so I can compare records in SQL 2000.
> >
> > MDL-1553-AandB-083630
> > MDL-1553-AandB-498103
> > MDL-1553-AndB-00060
> >
> > Would return either
> > 083630
> > 498103
> > 00060
> >
> > Or
> >
> > 83630
> > 498103
> > 60
> >
> > Wasn't sure if I could convert a varchar field to int and return a number
> > with leading zeros. The final results need to be int because on another
> > piece of the code I will be writing a less than comparison.
>
>

13 Sep 2006 7:21 PM

Roy Harvey

CREATE TABLE Example (s varchar(30))

INSERT Example VALUES ('MDL-1553-AandB-083630')
INSERT Example VALUES ('MDL-1553-AandB-498103')
INSERT Example VALUES ('MDL-1553-AndB-00060')

SELECT RIGHT(s, CHARINDEX('-', REVERSE(s))-1)
FROM Example

It would be simple to convert to int after that.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Sep 2006 12:06:02 -0700, Anonymous
<Anonym***@discussions.microsoft.com> wrote:

Show quote

>I want to select the characters to the right of a dash and convert it to an
>int so I can compare records in SQL 2000.
>
>MDL-1553-AandB-083630
>MDL-1553-AandB-498103
>MDL-1553-AndB-00060
>
>Would return either
>083630
>498103
>00060
>
>Or
>
>83630
>498103
>60
>
>Wasn't sure if I could convert a varchar field to int and return a number
>with leading zeros. The final results need to be int because on another
>piece of the code I will be writing a less than comparison.