Query in two tables involving chars

Question

Post reply

Query in two tables involving chars

nandoliveira

Old Hand

Points: 391
More actions
June 16, 2008 at 6:35 am

#192291

hi,i'm developing one software in ASP.NET/VB.NET using SQL Server 2005 and at this moment i need to make one special query, but is giving me some problems:
I have two tables:
1st: name errorwords and the fields that i need is txt_alt(varchar) and n_errors(int)
2st: name caracter and the field is only caracter(char),
they tables are not related but:
i need something like that:
Select txt_alt from errorwords where n_errors=0 and
txt_alt not like '% (select caracter from caracter) %'
this is giving error because "select caracter from caracter" returns chars like 'ø', '¢', 'Ā', 'ń', 'ē' ,'Ŗ' ,'æ' , 'ģ', 'Ķ', 'ß' and this char can be in any part of the string txt_alt
futhermore i need to check if the '?' appears only one time and in the end of the setence...
i probably should make some procedure but my sql knologe is limited and i never work with procedures....
Can anyone help?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

raja_saminathan SSCommitted Points: 1607 More actions · Answer 1

Can you explain clear by posting some sample datas and expected Results you are looking for

Rajesh

nandoliveira Old Hand Points: 391 More actions · Answer 2

Yes, i will try:

In the table errorwords i have sentences with some char in the sentences that are not from my language and i have to correct it....

But in some cases i have interrogations, so i need some query or procedure that goes to errorwords and check if is one question: {to be question can't appear none of the char from caracter table execpt the '?' and this one only in the end of the sentence}

==>> Table errorwords:

id txt_alt

1 hellø thiA sentRnce have bæd chars

2 this one is correct

3 this ? have two ?

4 this is one question ?

.....

==>> Table caracter

id_letter caracter

1 ?

2 ø

3 ¢

4 A

5 n

6 e

...

I hope now i explain better 🙂

Thanks fot the attencion

sgmunson SSC Guru Points: 110640 More actions · Answer 3

If I understood you correctly, you may need to take the record in your caracter table that has the ? in it and delete it. I think a query along the lines of the following might then work:

SELECT DISTINCT txt_alt

FROM errorwords JOIN caracter

WHERE n_errors = 0 AND

txt_alt NOT LIKE '%'+caracter+'%' AND

txt_alt NOT LIKE '%?%?' AND

(txt_alt LIKE '%?' OR txt_alt NOT LIKE '%?%')

Does that help?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

nandoliveira Old Hand Points: 391 More actions · Answer 4

Is giving me one errors and it says that is near the where...

thar are one thing that i don't understand wen the join is maded u put errorwords join caracter

and after in "txt_alt NOT LIKE '%'+caracter+'%' " how did i know that he is seing the fiel caracter of the table caracter?

Thanks fot the attencion 🙂

sgmunson SSC Guru Points: 110640 More actions · Answer 5

I suspect if you replace the word JOIN with a comma, that problem may go away.

On the LIKE operator, the expression 'abcde' LIKE '%b%' is TRUE, while the expression 'abcde' LIKE '%Æ%' is FALSE. Thus the NOT LIKE will invert the result. A % character within the string that FOLLOWS the LIKE operator is a wildcard that can represent any string of characters, or even no characters, while an underscore represents any single character, and reviewing that made me realize that I need to fix the conditions somewhat, so here's new code:

SELECT DISTINCT txt_alt

FROM errorwords, caracter

WHERE n_errors = 0 AND

txt_alt NOT LIKE '%'+caracter+'%' AND

txt_alt NOT LIKE '%?%?' AND

(txt_alt LIKE '%?' OR txt_alt NOT LIKE '%?_%')

Does that help?

Steve

(aka sgmunson)

:):):)

nandoliveira (6/18/2008)
Is giving me one errors and it says that is near the where...
thar are one thing that i don't understand wen the join is maded u put errorwords join caracter
and after in "txt_alt NOT LIKE '%'+caracter+'%' " how did i know that he is seing the fiel caracter of the table caracter?
Thanks fot the attencion 🙂

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Leandro Nuñez Grasshopper Points: 22 More actions · Answer 6

There is a easier way to do that, but the table must be full-text indexed.

Regards,

Leandro Nuñez.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Might be easiest to determine which characters are legal and look for anything that DOESN't match. I'd use patindex for that.

something like

PATINDEX('%[^0-9a-zA-Z .,!+-]%',MyColumnname)>0

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?