SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query for a plus sign


Query for a plus sign

Author
Message
AndrewSQLDBA
AndrewSQLDBA
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1752 Visits: 3427
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38933 Visits: 38508
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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38933 Visits: 38508
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 6431
Another way using Lynn's test table:


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





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27938 Visits: 39921
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.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7768 Visits: 7140
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 Sad.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7768 Visits: 7140
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7768 Visits: 7140
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search