Compare String to Column in SQL Server, and Return a value

Question

I'm not sure if I should create a View, Stored Procedure, or function for this, but here is what I have so far:

USE [HomeStorageQA2]

GO
DECLARE @SearchValue Varchar(255)
DEClare @Flag Varchar(255)
SET @Flag = NULL
SET @SearchValue = 'Inner point(s) > outer point(s);'
Declare @MainString Varchar(255)
Set @MainString = dbo.GetComments
if Charindex(@SearchValue,@MainString) > 0
begin
Set @Flag = 'True'
end
Select  'Flag' = @Flag
go

If I Set @MainString = [Some value containing SearchValue] , then the query returns True. I want to use this query for the rows in TestComments.Comments; so it's comparing the value of Comments to the @SearchValue, and returning True or False for each row.

I already have a Stored Procedure dbo.GetComments to find a match via an identifier called SummaryID in the TestComments table.

My final goal is to be able to create a Select statement with three columns of data: SummaryID, Comments, and the corresponding @Flag value for all rows in TestComments. Will this syntax be able to create what I need, and how do I set @MainString to the value I want?
Should this be created as a stored procedure, function, or view? (I'm assuming Stored Procedure or Function)

Funny enough, my predecessors already created tons of Stored Procedures and Functions, and very few views. — 5unnyr4y3
– 5unnyr4y3, Commented Jun 11, 2015 at 17:27

Mark · Accepted Answer · 2015-06-11 20:59:56Z

3

Here's a stored procedure version:

CREATE PROCEDURE FindMatches
    @searchValue varchar(MAX)
AS
BEGIN
SELECT SummaryID
    , Comments
    , CASE
        WHEN CHARINDEX(@searchValue, Comments) > 0 THEN 'True'
        ELSE 'False'
        END AS Flag
FROM TestComments
END

edited Jun 11, 2015 at 20:59

answered Jun 11, 2015 at 16:25

Mark

1927 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Over a year ago

What database are you using?

Mark Over a year ago

Sorry. I was in a rush when I wrote my original answer. Fixed and tested on my own rig. It should work for you now. :)

Drew · Accepted Answer · 2015-06-11 16:24:13Z

2

make it a proc/fnc if and only if it rises to the level of re-usability and coveted status that it belongs there. if you don't know the difference, then the answer is no. it is probably random gibberish not worthy of such status, less you desire a system of way crazy inching toward an inevitable face-palm

answered Jun 11, 2015 at 16:24

Drew

25k10 gold badges47 silver badges81 bronze badges

1 Comment

5unnyr4y3 Over a year ago

Thanks for the reality check; reuseability is definitely something to keep in mind. Thankfully, with Mark's answer we can use this to search for other comment flags.

Giorgi Nakeuri · Accepted Answer · 2015-06-11 16:13:40Z

1

For example you can create a scalar function that returns bit. The it is done like:

select SummaryID, Comments, dbo.FunctionName(Comments, @searchString)  
from TestComments

answered Jun 11, 2015 at 16:13

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

Compare String to Column in SQL Server, and Return a value

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related