Query with SELECT and SUBSTRING

Hello,
I'm a beginner in SQLServer and I'm trying to crite a query with a subst
ring but without success. I've got a field (String) in a table which con
tains a price formatted like that "AUD 2,000.10". I would like with a su
bstring (or something else) obtain something like "2000.10". Can somebod
y help me with that ?
Thanks a lot
Vincent
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Article poste via Voila News - http://www.news.voila.fr
Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP 3500
978]Hi Vincent,
Is it always AUD ?
try
select convert(money,right(col1,len(col1)-3))
I hope this helps
--
Greg O
http://www.sql-scripts.com
"MOTTE" <liste@.france-dev.com> wrote in message
news:c4agh9$src$1@.news.x-echo.com...
> Hello,
> I'm a beginner in SQLServer and I'm trying to crite a query with a subst
> ring but without success. I've got a field (String) in a table which con
> tains a price formatted like that "AUD 2,000.10". I would like with a su
> bstring (or something else) obtain something like "2000.10". Can somebod
> y help me with that ?
> Thanks a lot
> Vincent
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Article poste via Voila News - http://www.news.voila.fr
> Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP
3500978]

query to select substring of field?

If I want to do something like:
select substr(Field1,1,2), <other fields>
from Table
group by substr(Field1,1,2)
where Field1 is a 4-character text field, is my syntax above even close to
what I need? What I want to replicate is the situation where I'd have, say,
Field2 which would be the first two characters of Field1.
Thanks.
David"msnews.microsoft.com" <David@.David.com> wrote in message
news:%23uoYnkJ6FHA.268@.TK2MSFTNGP10.phx.gbl...
> If I want to do something like:
> select substr(Field1,1,2), <other fields>
> from Table
> group by substr(Field1,1,2)
> where Field1 is a 4-character text field, is my syntax above even close to
> what I need? What I want to replicate is the situation where I'd have,
> say, Field2 which would be the first two characters of Field1.
> Thanks.
> David
>
The function is SUBSTRING(). Look it up in Books Online for the full syntax.
I'm not sure what the purpose of GROUP BY is in your example but it is valid
in SQL Server to use expressions in GROUP BY. It isn't permitted in standard
SQL however.
David Portas
SQL Server MVP
--|||David,
Thanks for your help.
The purpose of the group by is that I'll be having averages taken in the
select statement (amongst the <other fields> ).
David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hLmdnT-YGIX0LureRVnyvw@.giganews.com...
> "msnews.microsoft.com" <David@.David.com> wrote in message
> news:%23uoYnkJ6FHA.268@.TK2MSFTNGP10.phx.gbl...
> The function is SUBSTRING(). Look it up in Books Online for the full
> syntax. I'm not sure what the purpose of GROUP BY is in your example but
> it is valid in SQL Server to use expressions in GROUP BY. It isn't
> permitted in standard SQL however.
> --
> David Portas
> SQL Server MVP
> --
>sql