Query for a plus sign

Question

Query for a plus sign

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
December 11, 2012 at 5:03 pm

#286083

Hello Everyone
I am attempting to query a column that the data has a plus sign. I know there is data, but nothing.
I have tried multiple wildcards, but still nothing seems to work. I have even tried : LIKE '%[^0-9]%'
This is some sample data:
5+
1+
etc...
Can anyone suggest a way to query this data.
Thank you in advance fr your help and suggestions
Andrew SQLDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

AndrewSQLDBA (12/11/2012)
Hello Everyone
I am attempting to query a column that the data has a plus sign. I know there is data, but nothing.
I have tried multiple wildcards, but still nothing seems to work. I have even tried : LIKE '%[^0-9]%'
This is some sample data:
5+
1+
etc...
Can anyone suggest a way to query this data.
Thank you in advance fr your help and suggestions
Andrew SQLDBA

Is the data type of the column varchar or nvarchar?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

This seems to work:

create table #TestTable(

TestStr varchar(10)

);

go

insert into #TestTable

values ('5+'),('1+'),('3-');

go

select * from #TestTable where TestStr like '%+%';

go

drop table #TestTable;

go

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Another way using Lynn's test table:

SELECT * FROM #TestTable WHERE CHARINDEX('+', TestStr) > 0

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Lowell SSC Guru Points: 323504 More actions · Answer 4

CELKO (12/11/2012)
In COBOL, the display and the internal format of data was the same. Each field had a PICTURE clause to define it. Your question makes sense only in that context.

COBOL was never mentioned, and you are purposefully misrepresenting and misreading the original question. The question is about a specific character existing in a field. anything else you read into the question is nonsense. Worse, your strained comparison to try and bring a PICTURE clause into the question is just plain ridiculous.

This is SQL; columns are abstract data types that can be implemented any way at all. As with any -- repeat, ANY -- tiered architecture, all of the display formatting is done in a presentation layer. NEVER in the database.

the original poster never mentioned anything about formatting the data. he is clearly simply trying to find the substring of a string containing a specific character.

This is not a little miscegenation; this is the basis of C/S. You are the flat earth kid in a geography class.[/quote]

why not call him a witch and call for burning him at the stake? your constant attempts to belittle posters is worse than unprofessional. You are an embarrassment to the SQL community as a whole, and i wish you simply stop posting. Look for yourself: in your last, say 50 posts, have you helped anyone, at all? You simply went on diatribes about issues, but made sure to add a few insults along the way.

I

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

Lowell (12/12/2012)
why not call him a witch and call for burning him at the stake?
I

Speaking from experience, I have seen some SQL code where the developer literally should have been burned at the stake! Witch or not.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

Are you trying to make a numeric comparison against the data? That won't work in SQL Server because the "+" (or "-") sign has to be leading, not trailing. Some SQL engines may work with trailing signs, but SQL Server doesn't :(.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 7

CELKO (12/11/2012)
In COBOL, the display and the internal format of data was the same. Each field had a PICTURE clause to define it. Your question makes sense only in that context.
This is SQL; columns are abstract data types that can be implemented any way at all. As with any -- repeat, ANY -- tiered architecture, all of the display formatting is done in a presentation layer. NEVER in the database.
This is not a little miscegenation; this is the basis of C/S.
Editor: Removed unprofessional comment.

No, sometimes pre-formatting is done in the RDBMS for convenience. Theorists may decry that, but it happens all the time. HTML is just a variation of that, and it's stored in RDBMs all the time.

Btw, no RDBMS ever has anything whatsoever to do with "miscegenation", which involves racial mixing! How on earth do you figure that "miscegenation" is even within LIGHT YEARS on any SQL topic???

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 8

ScottPletcher (12/12/2012)
CELKO (12/11/2012)
In COBOL, the display and the internal format of data was the same. Each field had a PICTURE clause to define it. Your question makes sense only in that context.
This is SQL; columns are abstract data types that can be implemented any way at all. As with any -- repeat, ANY -- tiered architecture, all of the display formatting is done in a presentation layer. NEVER in the database.
This is not a little miscegenation; this is the basis of C/S.
Editor: Removed unprofessional comment.
No, sometimes pre-formatting is done in the RDBMS for convenience. Theorists may decry that, but it happens all the time. HTML is just a variation of that, and it's stored in RDBMs all the time.
Btw, no RDBMS ever has anything whatsoever to do with "miscegenation", which involves racial mixing! How on earth do you figure that "miscegenation" is even within LIGHT YEARS on any SQL topic???

So, Mr. Celko, still no explanation as to how-the-heck race figured into this in any way?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

GSquared SSC Guru Points: 260824 More actions · Answer 9

ScottPletcher (12/13/2012)
ScottPletcher (12/12/2012)
CELKO (12/11/2012)
In COBOL, the display and the internal format of data was the same. Each field had a PICTURE clause to define it. Your question makes sense only in that context.
This is SQL; columns are abstract data types that can be implemented any way at all. As with any -- repeat, ANY -- tiered architecture, all of the display formatting is done in a presentation layer. NEVER in the database.
This is not a little miscegenation; this is the basis of C/S.
Editor: Removed unprofessional comment.
No, sometimes pre-formatting is done in the RDBMS for convenience. Theorists may decry that, but it happens all the time. HTML is just a variation of that, and it's stored in RDBMs all the time.
Btw, no RDBMS ever has anything whatsoever to do with "miscegenation", which involves racial mixing! How on earth do you figure that "miscegenation" is even within LIGHT YEARS on any SQL topic???
So, Mr. Celko, still no explanation as to how-the-heck race figured into this in any way?

Joe is constitutionally incapable of admitting errors. He used the wrong word. Not sure what word he meant to use, but definitely the wrong one. Maybe, judging by the context, "misconception" was what he was aiming for. But I'm guessing at this point.

I'm pretty sure he wasn't trying to enter race into the subject. He probably thinks he knows what the word means, but is simply wrong. Never yet seen him admit to an error, even one as eggregious and obvious as this, so don't expect him to start.

But it's really no big deal. Just a malapropism is all. Laugh at it and ignore it. (Actually, that last bit of advice applies to a lot of Joe's posts. Laugh at them and ignore them.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon