query

Hi,
I have some models that have hyhen in between. e.g.1200LD-VCD, abc-1234
May I know how to remove the hyhen.
Thank you
On Wed, 30 Nov 2005 18:35:02 -0800, Tiffany wrote:

>Hi,
>I have some models that have hyhen in between. e.g.1200LD-VCD, abc-1234
>May I know how to remove the hyhen.
>Thank you
>
Hi Tiffany,
If you mean that the hyphen is embedded in the data in your table and
you want to show the data without the hyphen, then use
SELECT REPLACE(YourColumn, '-', '')
FROM ...
If that's not what you want, then please check out www.aspfaq.com/5006,
an excellent description of what information you need to provide if you
want to help us help you.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I tried the script but it only remove the hyphen in some models but not all.
Is there a full proof way?
Also, sometimes, i have models that have more than one hypen. e.g.
abc-ded-123-dvd
Kindly advise. Thanks
"Hugo Kornelis" wrote:

> On Wed, 30 Nov 2005 18:35:02 -0800, Tiffany wrote:
>
> Hi Tiffany,
> If you mean that the hyphen is embedded in the data in your table and
> you want to show the data without the hyphen, then use
> SELECT REPLACE(YourColumn, '-', '')
> FROM ...
> If that's not what you want, then please check out www.aspfaq.com/5006,
> an excellent description of what information you need to provide if you
> want to help us help you.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Sun, 4 Dec 2005 22:46:02 -0800, Tiffany wrote:

>Hi Hugo,
>I tried the script but it only remove the hyphen in some models but not all.
>Is there a full proof way?
Hi Tiffany,
I'm not sure what "script" you tried, since I posted only a partial
suggestion that you have to complete yourself. For further
trouble-shooting, I can only repeat what I wrote in my original reply:
If that's not what you want, then please check out www.aspfaq.com/5006,
an excellent description of what information you need to provide if you
want to help us help you.

>Also, sometimes, i have models that have more than one hypen. e.g.
>abc-ded-123-dvd
How do you want to handle these? If you want to remove all hyphens, then
stick with the REPLACE I suggested. If you want to remove only some of
them, then you'll first have to explain me how to decide which hyphens
should go and which should stay. I can then try to translate your
requirements into SQL.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I used the below script which you had given me.
SELECT REPLACE(model, '-', '') FROM ctv_stage
model is the column which i want to remove all the hyphens. ctv_stage is the
table name. data type for model is varchar.
May I know what is the problem.
Thanks!
"Hugo Kornelis" wrote:

> On Sun, 4 Dec 2005 22:46:02 -0800, Tiffany wrote:
>
> Hi Tiffany,
> I'm not sure what "script" you tried, since I posted only a partial
> suggestion that you have to complete yourself. For further
> trouble-shooting, I can only repeat what I wrote in my original reply:
> If that's not what you want, then please check out www.aspfaq.com/5006,
> an excellent description of what information you need to provide if you
> want to help us help you.
>
> How do you want to handle these? If you want to remove all hyphens, then
> stick with the REPLACE I suggested. If you want to remove only some of
> them, then you'll first have to explain me how to decide which hyphens
> should go and which should stay. I can then try to translate your
> requirements into SQL.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 5 Dec 2005 17:59:01 -0800, Tiffany wrote:

>Hi Hugo,
>I used the below script which you had given me.
>SELECT REPLACE(model, '-', '') FROM ctv_stage
>model is the column which i want to remove all the hyphens. ctv_stage is the
>table name. data type for model is varchar.
>May I know what is the problem.
Hi Tiffany,
Did you already check www.aspfaq.com/5006, as I requested you two times
before? That article clearly describes the THREE elements I need to be
able to help you.
First: DDL, or CREATE TABLE statement.
Second: Sample data, in the form of INSERT statements.
Third: Desired results.
Here's an example of how you should build your next post. You can copy
and paste the SQL below into Query Analyzer and execute to see that the
results of this query are as I expect them: without hyphens. Now please
post ssome SQL that I can run on my end to reproduce your problem.
Without that, I'm unable to help you further.
-- Create a table for testing
CREATE TABLE ctv_stage
(model varchar(100) NOT NULL)
go
-- Put some sample data in it
INSERT INTO ctv_stage (model)
SELECT '1200LD-VCD'
UNION ALL
SELECT 'abc-1234'
UNION ALL
SELECT 'abc-ded-123-dvd'
UNION ALL
SELECT 'No hyphens here'
go
-- Check test data
SELECT model FROM ctv_stage
-- Test the query
SELECT REPLACE(model, '-', '') FROM ctv_stage
go
-- Clean up my test DB
DROP TABLE ctv_stage
This is the output I got when running the scribt above:
model
1200LD-VCD
abc-1234
abc-ded-123-dvd
No hyphens here
1200LDVCD
abc1234
abcded123dvd
No hyphens here
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)