STORED PROCEDURE: String Comparison 
Author Message
 STORED PROCEDURE: String Comparison

I've been working on this all day and can't quite figure it out T-SQL.  

I've got a table that looks like this:

UniqueID  Col1  Col2  Col3
1         A     B     C
2         D     E     F
3         G     H     I

I want to loop through each row one column at a time and compare each field with a string.

For instance, in pseudo-code:
If A = StringToCompare Then
  Return A
ElseIf B = StringToCompare Then
  Return B
.
.
and so on until a match is found
.
.
Else
  Return DefaultValue
End If

For the string comparisons I was using:
CHARINDEX(SubString, StringToCompare, 1)

If this makes sense and if you've got a nifty way for me to loop through a recordset please let me know.  Thanks in advance.

Brian Beaudet
Web Applications Developer
B Squared Web Applications

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!

  _alt.0
< 1K Download


Sat, 22 May 2004 02:55:03 GMT
 STORED PROCEDURE: String Comparison

Could you give us a little more "real-world" explanation of what you're trying to do along with DDL (create table) statements and sample data (INSERT statements)? Based on your simple description I'd say that what you are trying to do is horrid. I'm guessing there is a much better way to accomplish what you need done without having to "loop through" anything. With SQL, the moment you say "loop through", go ahead and expect the performance to take orders of magnitude longer than it should. Give us some real-world info on your problem and lets see if we can come up with a set based solution.

Zach


  I've been working on this all day and can't quite figure it out T-SQL.  

  I've got a table that looks like this:

  UniqueID  Col1  Col2  Col3
  1         A     B     C
  2         D     E     F
  3         G     H     I

  I want to loop through each row one column at a time and compare each field with a string.

  For instance, in pseudo-code:
  If A = StringToCompare Then
    Return A
  ElseIf B = StringToCompare Then
    Return B
  .
  .
  and so on until a match is found
  .
  .
  Else
    Return DefaultValue
  End If

  For the string comparisons I was using:
  CHARINDEX(SubString, StringToCompare, 1)

  If this makes sense and if you've got a nifty way for me to loop through a recordset please let me know.  Thanks in advance.

  Brian Beaudet
  Web Applications Developer
  B Squared Web Applications

  *** Sent via Developersdex http://www.developersdex.com ***
  Don't just participate in USENET...get rewarded for it!



Sat, 22 May 2004 03:07:27 GMT
 STORED PROCEDURE: String Comparison
To clarify my question:

I have one static table called SMEData:
CREATE TABLE [SMEData] (
        [SMEID] [int] NOT NULL ,
        [Area1] [nvarchar] (50) ,
        [Area2] [nvarchar] (50) ,
        [Area3] [nvarchar] (50) ,
        [Area4] [nvarchar] (50) ,
        [Area5] [nvarchar] (50)
)

With output like this:
SMEID   Area1    Area2     Area3   Area4    Area5
-------------------------------------------------
1       Nuclear  Energy    NULL    NULL     NULL
2       Pass     Throw     Pitch   Scramble Audible
3       Sack     Blitz     NULL    NULL     NULL
4       Tackle   Intercept Smash   NULL     NULL
5       Catch    Receive   Run     Score    NULL

I want to compare the values in the above table with the result of another SELECT command that outputs like this:

KBID  FullSearch
----------------------------------------------
1     SOLUTIONTITLE1 PROBLEMTEXT1 SOLUTIONTEXT1

With FullSearch being a single string concatenated (and converted to all uppercase characters)from SolutionTitle, ProblemText and SolutionText fields from another table.

The goal is to compare values in row 1 of SMEData (one field at a time) to the string in FullSearch and if a match is found return the SMEID for that row.  If row 1 holds no matches, then move to row 2, and so on.  By the way, I would compare both strings in uppercase only.

Does this make more sense?

I picture myself using some form of:

Brian Beaudet
Web Applications Developer
B Squared Web Applications

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 22 May 2004 03:34:55 GMT
 STORED PROCEDURE: String Comparison
I'm going to make 1 important assumption about your data. In your
explanation you say that you want to start with the first row, 1, in
SMEdata, and check each one sequntially. So, is it a safe assumption that
your ID is sequential and in the order you want? Also, you say that you want
to compare them based on the output of another select statement, is that
select statement always going to return 1 result? Or are there multiple
results?

If you only have one result in the FullSearch result, you can use vars:


select top 1 SMEID, Area1, Area2, Area3, Area4, Area5
from SMEData
where (





           )
order by SMEID

Does this help?

Zach


To clarify my question:

I have one static table called SMEData:
CREATE TABLE [SMEData] (
[SMEID] [int] NOT NULL ,
[Area1] [nvarchar] (50) ,
[Area2] [nvarchar] (50) ,
[Area3] [nvarchar] (50) ,
[Area4] [nvarchar] (50) ,
[Area5] [nvarchar] (50)
)

With output like this:
SMEID   Area1    Area2     Area3   Area4    Area5
-------------------------------------------------
1 Nuclear Energy    NULL    NULL     NULL
2 Pass Throw    Pitch   Scramble Audible
3 Sack Blitz    NULL    NULL     NULL
4 Tackle Intercept Smash   NULL     NULL
5 Catch Receive   Run    Score    NULL

I want to compare the values in the above table with the result of another
SELECT command that outputs like this:

KBID  FullSearch
----------------------------------------------
1     SOLUTIONTITLE1 PROBLEMTEXT1 SOLUTIONTEXT1

With FullSearch being a single string concatenated (and converted to all
uppercase characters)from SolutionTitle, ProblemText and SolutionText fields
from another table.

The goal is to compare values in row 1 of SMEData (one field at a time) to
the string in FullSearch and if a match is found return the SMEID for that
row.  If row 1 holds no matches, then move to row 2, and so on.  By the way,
I would compare both strings in uppercase only.

Does this make more sense?

I picture myself using some form of:


know how to move through the SMEData table to perform this comparison.  I've
got it working fine as several database calls in my ASP page but I'm trying
to get this into one stored procedure in an effort to make my web
application more efficient.

Brian Beaudet
Web Applications Developer
B Squared Web Applications

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 22 May 2004 03:55:55 GMT
 STORED PROCEDURE: String Comparison
Brian,

If you really what what you say you do, you can get it this way:

select
  case when exists (
    select 1 from T
    where StringToCompare = Col1
    or StringToCompare = Col2
    or StringToCompare = Col3
) then StringToCompare
else DefaultValue as QueryResult

Steve Kass
Drew University

Quote:

> I've been working on this all day and can't quite figure it out T-SQL.

> I've got a table that looks like this:

> UniqueID  Col1  Col2  Col3
> 1         A     B     C
> 2         D     E     F
> 3         G     H     I

> I want to loop through each row one column at a time and compare each field with a string.

> For instance, in pseudo-code:
> If A = StringToCompare Then
>   Return A
> ElseIf B = StringToCompare Then
>   Return B
> .
> .
> and so on until a match is found
> .
> .
> Else
>   Return DefaultValue
> End If

> For the string comparisons I was using:
> CHARINDEX(SubString, StringToCompare, 1)

> If this makes sense and if you've got a nifty way for me to loop through a recordset please let me know.  Thanks in advance.

> Brian Beaudet
> Web Applications Developer
> B Squared Web Applications

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

>   ------------------------------------------------------------------------
>              Name: _alt.0
>    _alt.0    Type: Hypertext Markup Language (text/html)
>          Encoding: Quoted-Printable



Sat, 22 May 2004 03:54:33 GMT
 STORED PROCEDURE: String Comparison
Brian,

  I would junk the denormalization of the table and proceed this way:

create view NormalSMEData as
select SMEID, Area1 as Dat from SMEData
union all
select SMEID, Area2 from SMEData
union all
select SMEID, Area3 from SMEData
union all
select SMEID, Area4 from SMEData
union all
select SMEID, Area5 from SMEData

select min(SMEID) from NormalSMEData
where upper(fullseearch) = upper(dat)

Steve Kass
Drew University

Quote:

> To clarify my question:

> I have one static table called SMEData:
> CREATE TABLE [SMEData] (
>         [SMEID] [int] NOT NULL ,
>         [Area1] [nvarchar] (50) ,
>         [Area2] [nvarchar] (50) ,
>         [Area3] [nvarchar] (50) ,
>         [Area4] [nvarchar] (50) ,
>         [Area5] [nvarchar] (50)
> )

> With output like this:
> SMEID   Area1    Area2     Area3   Area4    Area5
> -------------------------------------------------
> 1       Nuclear  Energy    NULL    NULL     NULL
> 2       Pass     Throw     Pitch   Scramble Audible
> 3       Sack     Blitz     NULL    NULL     NULL
> 4       Tackle   Intercept Smash   NULL     NULL
> 5       Catch    Receive   Run     Score    NULL

> I want to compare the values in the above table with the result of another SELECT command that outputs like this:

> KBID  FullSearch
> ----------------------------------------------
> 1     SOLUTIONTITLE1 PROBLEMTEXT1 SOLUTIONTEXT1

> With FullSearch being a single string concatenated (and converted to all uppercase characters)from SolutionTitle, ProblemText and SolutionText fields from another table.

> The goal is to compare values in row 1 of SMEData (one field at a time) to the string in FullSearch and if a match is found return the SMEID for that row.  If row 1 holds no matches, then move to row 2, and so on.  By the way, I would compare both strings in uppercase only.

> Does this make more sense?

> I picture myself using some form of:

> Brian Beaudet
> Web Applications Developer
> B Squared Web Applications

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sat, 22 May 2004 04:01:02 GMT
 STORED PROCEDURE: String Comparison
Change the "=" to "<>"

Zach


Quote:
> I'm going to make 1 important assumption about your data. In your
> explanation you say that you want to start with the first row, 1, in
> SMEdata, and check each one sequntially. So, is it a safe assumption that
> your ID is sequential and in the order you want? Also, you say that you
want
> to compare them based on the output of another select statement, is that
> select statement always going to return 1 result? Or are there multiple
> results?

> If you only have one result in the FullSearch result, you can use vars:


> select top 1 SMEID, Area1, Area2, Area3, Area4, Area5
> from SMEData
> where (





>            )
> order by SMEID

> Does this help?

> Zach



> To clarify my question:

> I have one static table called SMEData:
> CREATE TABLE [SMEData] (
> [SMEID] [int] NOT NULL ,
> [Area1] [nvarchar] (50) ,
> [Area2] [nvarchar] (50) ,
> [Area3] [nvarchar] (50) ,
> [Area4] [nvarchar] (50) ,
> [Area5] [nvarchar] (50)
> )

> With output like this:
> SMEID   Area1    Area2     Area3   Area4    Area5
> -------------------------------------------------
> 1 Nuclear Energy    NULL    NULL     NULL
> 2 Pass Throw    Pitch   Scramble Audible
> 3 Sack Blitz    NULL    NULL     NULL
> 4 Tackle Intercept Smash   NULL     NULL
> 5 Catch Receive   Run    Score    NULL

> I want to compare the values in the above table with the result of another
> SELECT command that outputs like this:

> KBID  FullSearch
> ----------------------------------------------
> 1     SOLUTIONTITLE1 PROBLEMTEXT1 SOLUTIONTEXT1

> With FullSearch being a single string concatenated (and converted to all
> uppercase characters)from SolutionTitle, ProblemText and SolutionText
fields
> from another table.

> The goal is to compare values in row 1 of SMEData (one field at a time) to
> the string in FullSearch and if a match is found return the SMEID for that
> row.  If row 1 holds no matches, then move to row 2, and so on.  By the
way,
> I would compare both strings in uppercase only.

> Does this make more sense?

> I picture myself using some form of:


don't
> know how to move through the SMEData table to perform this comparison.
I've
> got it working fine as several database calls in my ASP page but I'm
trying
> to get this into one stored procedure in an effort to make my web
> application more efficient.

> Brian Beaudet
> Web Applications Developer
> B Squared Web Applications

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sat, 22 May 2004 04:17:58 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Stored procedure comparison

2. Strange Results With NULL Comparison in Stored Procedures

3. Stored Procedure - Date Comparison

4. Stored Procedure Comparison

5. Calling a Java Stored Procedure from another Java Stored Stored Procedure

6. Long strings in stored procedure

7. String truncation when calling stored procedures from Active Server Pages

8. String truncation when calling stored procedures from Active Server Pages

9. Return a string longer than 8000 chars from stored procedure

10. String Building Error in SQL in Stored Procedure

11. Error String - Stored Procedure

12. Maximum Length of String For Dynamic Stored Procedures


 
Powered by phpBB® Forum Software