Right characters from ~

16 Sep 2005 11:57 AM

Jaap

Hai,
Who can help me ??
I'm making a query from a table.
In that table there is a column with the name NR_
The rows of that column give a result as
~2000252
~2003
~26578
What i want as result, the most right character from the ~character and as
result in the same column
200252
2003
26578
Who can help me ?

16 Sep 2005 12:04 PM

JT

There are several string related functions that will do what you want. Look
up documentation on right(), substring() and replace()

Show quote

"Jaap" <J***@discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@microsoft.com...
> Hai,
> Who can help me ??
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?

16 Sep 2005 12:13 PM

John Bell

Hi

Try something like:

SELECT RIGHT (col1,CHARINDEX('~',REVERSE(col1))-1)
FROM
( SELECT '~2000252' as col1
UNION ALL SELECT '~2003'
UNION ALL SELECT '~26578'
UNION ALL SELECT 'abc~def' ) A

John

Show quote

"Jaap" <J***@discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@microsoft.com...
> Hai,
> Who can help me ??
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?

16 Sep 2005 1:45 PM

Jaap

yes, this is great it works
many thanks

Show quote

"John Bell" wrote:

> Hi
>
> Try something like:
>
> SELECT RIGHT (col1,CHARINDEX('~',REVERSE(col1))-1)
> FROM
> ( SELECT '~2000252' as col1
> UNION ALL SELECT '~2003'
> UNION ALL SELECT '~26578'
> UNION ALL SELECT 'abc~def' ) A
>
> John
>
> "Jaap" <J***@discussions.microsoft.com> wrote in message
> news:B0A0060F-EA08-4AE0-A516-67C4623564CA@microsoft.com...
> > Hai,
> > Who can help me ??
> > I'm making a query from a table.
> > In that table there is a column with the name NR_
> > The rows of that column give a result as
> > ~2000252
> > ~2003
> > ~26578
> > What i want as result, the most right character from the ~character and as
> > result in the same column
> > 200252
> > 2003
> > 26578
> > Who can help me ?
>
>
>

16 Sep 2005 12:23 PM

Hari Prasad

Hi,

Use the below query

select replace(column_name,'~','') as column from table_name

Thanks
Hari
SQL Server MVP

Show quote

"Jaap" <J***@discussions.microsoft.com> wrote in message
news:B0A0060F-EA08-4AE0-A516-67C4623564CA@microsoft.com...
> Hai,
> Who can help me ??
> I'm making a query from a table.
> In that table there is a column with the name NR_
> The rows of that column give a result as
> ~2000252
> ~2003
> ~26578
> What i want as result, the most right character from the ~character and as
> result in the same column
> 200252
> 2003
> 26578
> Who can help me ?