Solved

Using'replace'in SQL

Posted on 2009-05-17
3
180 Views
Last Modified: 2012-05-07
Hi Experts
I have a routine to generate a list of unique diagnoses from some health records, but I need to remove the question marks

Sql = ""
Sql = Sql & "Insert into Distinct_Diagnoses [Desc] "
Sql = Sql & " select distinct [Diagnosis] from PATIENT_PROBLEMS "

is giving me
?Asthma
Asthma
?Stroke
Stroke
etc

The following change to the SQL gives a "type mismatch" error
Sql = Sql & "select distinct '" & (Replace [Diagnosis] , [?], "" ) & "'"

Any other way of doing the necessary "replace''?

Many thanks

0
Comment
Question by:peterdarazs
  • 2
3 Comments
 
LVL 2

Expert Comment

by:d1rtyw0rm
ID: 24405249
Correct me if i'm wrong but it seem that you have duplicated records.

You can simply filter them with a where clause like that :

Sql = ""
Sql = Sql & "Insert into Distinct_Diagnoses [Desc] "
Sql = Sql & " select distinct [Diagnosis] from PATIENT_PROBLEMS WHERE [Diagnosis] NOT LIKE '?%"
0
 
LVL 2

Accepted Solution

by:
d1rtyw0rm earned 500 total points
ID: 24405259
oups ....

Sql = Sql & " select distinct [Diagnosis] from PATIENT_PROBLEMS WHERE [Diagnosis] NOT LIKE '?%'"

Sorry ;P
0
 

Author Closing Comment

by:peterdarazs
ID: 31582324
ok, thanks - that might just be the trick . Many thanks

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help With Database JOIN 7 30
Sql server function help 15 38
Query Syntax 17 43
Requesting help with creating an SQL query with 2 tables 6 27
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question