Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query for a plus sign Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 5:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
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
Post #1395360
Posted Tuesday, December 11, 2012 5:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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

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)
Post #1395364
Posted Tuesday, December 11, 2012 5:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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





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)
Post #1395365
Posted Tuesday, December 11, 2012 6:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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!
Post #1395379
Posted Tuesday, December 11, 2012 6:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:15 PM
Points: 1,945, Visits: 2,861
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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1395384
Posted Wednesday, December 12, 2012 11:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1395823
Posted Wednesday, December 12, 2012 5:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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!
Post #1395935
Posted Wednesday, December 12, 2012 5:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 1,970, Visits: 2,911
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1395945
Posted Wednesday, December 12, 2012 5:48 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 1,970, Visits: 2,911
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1395949
Posted Thursday, December 13, 2012 4:54 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 1,970, Visits: 2,911
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1396435
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse