May 13, 2009 at 3:19 pm
I have a binary field that contains the following test data: 1, 2, 3
I am trying to set up a script that will find this record based on the number 3. The following script works if I enter the entire string (1, 2, 3) in the where clause but it will not return results based on the 3. Any ideas on how I can accomplish this? Any help will be greatly appreciated.
select PART_ID, dbo.HexToChar(BITS) as BITS
from PART_PO_BINARY
where
dbo.HexToChar(BITS) LIKE '3'
I have tried the following:
LIKE '*3'
LIKE ', 3'
IN ('3')
etc.....
May 13, 2009 at 3:54 pm
Try this
LIKE '%3'
You have to have wildcards on any unanchored sides. Also, the LIKE wildcards are "%" and "_", not "*" and "?" as you would use for filenames.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 3:58 pm
Perfect! Thanks for the lesson. It is greatly appreciated.
May 13, 2009 at 4:10 pm
**** sigh. Spent too much time making up examples, so RBarry beat me to the answer....Here 'tis anyway****
I don't recognize the HexToChar() funciton.
String matching wildcards with the LIKE comparison are a bit different in T-SQL than other languages. Where many would use an asterisk (*), T-SQL uses the percent sign (%). Some languages (like the DOS command prompt) use a question mark to represent any single character, whereas T-SQL uses an underscore (_). With data like this:TestField
---------
John
Martin
Thomas
Barry
Brian
JosephSelect TestField from MyTable where TestField like 'J%'returnsJohn
Joseph
Select TestField from MyTable where TestField like '%A%'returns[Code]Martin
Thomas
Barry
Brian[/code]
Select TestField from MyTable where TestField like '_A%'returns[Code]Martin
Barry[/code]
May 13, 2009 at 4:32 pm
john.arnott (5/13/2009)
**** sigh. Spent too much time making up examples, so RBarry beat me to the answer....Here 'tis anyway****...
Heh, I've been there too John. 🙂 Besides, really good examples like yours are always valuable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 8:36 am
I appreciate the examples and the time you guys spent helping me on this.
I have another issue with this project. This field will hold a list of Quality Clauses (1, 2, 3, 86, 210, etc...) that will print on our PO's.
Lets say the Quality department calls and wants me to find all parts that have a quality clause of 3 and remove them. I now have the script to find these but how would I go about removing the 3, spaces and commas included?
If they want me to add a clause number how would I go about adding the clause number and do it in a sequential manner? For example if they want me to add clause 4 to the example above how would I script it in behind number 3.
Any help will be greatly appreciated.
May 14, 2009 at 9:31 am
bpowers (5/14/2009)
...I have another issue with this project. This field will hold a list of Quality Clauses (1, 2, 3, 86, 210, etc...) that will print on our PO's.Lets say the Quality department calls and wants me to find all parts that have a quality clause of 3 and remove them. I now have the script to find these but how would I go about removing the 3, spaces and commas included?
If they want me to add a clause number how would I go about adding the clause number and do it in a sequential manner? For example if they want me to add clause 4 to the example above how would I script it in behind number 3...
This really gets into why you shouldn't have columns like this in the first place: They are very non-relational and as a consequence you have to go through all kinds of stupid hoops and tricks that would be completely unnecessary if you made it relational instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 9:37 am
I am learning this. There are several columns like this in our ERP system, Infor - Visual Enterprise.
May 14, 2009 at 11:04 am
That said, assuming that you cannot convert these to a more relational design, then your best approach is probably to parse the CSV field into a table variable or temporary table (see this article[/url]), manipulate it as needed and then rejoin it back into a CSV field (see this article[/url]). This may not always be the fastest approach, but it is the most general and reusable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 1:34 pm
That will work. Thank you very much for the guidance.
May 14, 2009 at 2:25 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 4:07 pm
If the vendor can guarantee a consistent format, then the built-in function REPLACE could be used.
This field will hold a list of Quality Clauses (1, 2, 3, 86, 210, etc...) that will print on our PO's.
Lets say the Quality department calls and wants me to find all parts that have a quality clause of 3 and remove them. I now have the script to find these but how would I go about removing the 3, spaces and commas included?
The below SQL assumes that each "field" is delimited by a comma including leading and trailing commas, so that the following would not be updated:
'3,4,5,' because there is no leading comma
',1,2,3' would not be updated because there is no trailing comma.
The leading and trailing commas are needed to identify the start and end of the field, otherwise, removing a '3' might change ',38' to ',8', which is not the desired result.
if object_id ('tempdb..#foo') is not null drop table #foo
create table #foo
(JunkSave varbinary(64)
,JunkExpected varbinary(64)
,JunkNew varbinary(64)
)
insert into #foo
( JunkSave
, JunkExpected
)
select CAST(',1,2,3,86,210,' as varbinary(64) )
,CAST(',1,2,86,210,' as varbinary(64) )
update#foo
setJunkNew = CAST(REPLACE( JunkSave , ',3,',',') as varbinary(255) )
selectJunkSave, JunkExpected, JunkNew
, CASE WHEN JunkExpected = JunkNew THEN 'SAME'
ELSE 'Different'
END AS WorkStatus
,cast ( JunkSave as varchar(256) ) as JunkSave_VC
,cast ( JunkExpected as varchar(256) ) as JunkExpected_VC
,cast ( JunkNew as varchar(256) ) as JunkNew_VC
from#foo
if object_id ('tempdb..#foo') is not null drop table #foo
SQL = Scarcely Qualifies as a Language
May 15, 2009 at 7:04 am
That is good stuff. Thank you very much.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply