Need to use a variable in an SQL Server string function

Question

I am not a great SQL user and am looking to solve what may be a simple problem. While I eventually will need to "loop thru" a list of strings that is the result of a single column query, I first need to solve this problem:

I would like to use a variable in an SQL Server string function. Here is an example piece of code:

declare @STR nvarchar(50)

set @STR = 'ceo'

SELECT
   Document_Text.DocText_ID,
   SUBSTRING(Document_Text.DocText, CHARINDEX(@STR, Document_Text.DocText)-125, 250) as SubText
FROM
   Document_Text
WHERE
   Document_Text.DocText like '%@STR%'

As I described above, I will eventually use the results of a single column query (~200 values) in place of @STR in such a query.

From an application standpoint, consider the following:

Document_Text holds columns for UserID, DocID, DocURL, and DocText - the last being a text form of a resume. The STR is a short query word(s) - nvarchar(50) - that is searched upon (I know that this may not seem intuitive as a search app, but there is another form of point-n-click processing that uses the results of this query that "makes it make sense").
My eventual query will grab job role or functional area key words from a single column of another table in the same database.

If anyone has suggestions for this next step, I'd appreciate also. Thanks.

You're going to need to use full text indexing for this. msdn.microsoft.com/en-us/library/cc879306.aspx — Jon Seigel
– Jon Seigel, Commented Jun 23, 2011 at 2:42
If you're thinking of looping, you're not in the SQL mindset yet :-) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 23, 2011 at 6:12
Although the language 'SQL' is officially pronounced "essquelle", the product 'SQL Server' is pronounced "sequel Sir Vurr", therefore it should be "a SQL Server string function" (not "an"). Probably not worth an edit, though ;) — onedaywhen
– onedaywhen, Commented Jun 23, 2011 at 7:33
looking to Join per Tim Franklin's explanation below... agreed that looping is loopy. — Dave Ef
– Dave Ef, Commented Jun 23, 2011 at 17:12
Also... the resume text has already been pushed into a large nvarchar(MAX) field. I did do an full-text index, but as I thought further about the issue I experimented to see if LIKE %@STR% would give me same results as CONTAINS. In this specific case, it appears to have... still further hacking to go for final validation. Thanks for the heads up. — Dave Ef
– Dave Ef, Commented Jun 23, 2011 at 17:16

JStead · Accepted Answer · 2011-06-23 03:10:58Z

2

declare @STR nvarchar(50)

set @STR = 'ceo'

SELECT
   Document_Text.DocText_ID,
   SUBSTRING(Document_Text.DocText, CHARINDEX(@STR, Document_Text.DocText)-125, 250) as SubText
FROM
   Document_Text
WHERE
   Document_Text.DocText like '%'+@STR+'%'
OPTION (RECOMPILE)

answered Jun 23, 2011 at 3:10

JStead

1,73011 silver badges12 bronze badges

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

1 Comment

Dave Ef Over a year ago

OK - this one worked for the initial case. Thanks!... I should have delved more into expression building. Lesson learned.

tim franklin · Accepted Answer · 2011-06-23 09:35:56Z

DECLARE   @Document_Text TABLE(UserID INT , DocText_ID INT , DocURL NVARCHAR(100),DocText NVarchar(4000))
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES (1,1,'Blah','Ceo blah blah blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (2,1,'Blah','blah blah blah blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (3,1,'Blah','blah blah Manager blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (4,1,'Blah','blah blah blah blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (5,1,'Blah','blah blah blah blah blah time time time') 
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (6,1,'Blah','Cblah blah blah blah blah time time time') 
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (7,1,'Blah','Cblah blah blah blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (8,1,'Blah','blah Ceo blah blah blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (9,1,'Blah','blah blah blah blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (10,1,'Blah','blah blah Programmer blah blah time time time' )
INSERT INTO @Document_Text (UserID , DocText_ID , DocURL,DocText) VALUES  (11,1,'Blah','blah blah blah blah blah time time time' )


DECLARE  @JobRole TABLE (JR_ID INT , JobRole NVARCHAR(50))
INSERT INTO @Jobrole (JR_ID , JobRole ) VALUES (1,'Programmer') 
INSERT INTO @Jobrole (JR_ID , JobRole ) VALUES (2,'Ceo') 
INSERT INTO @Jobrole (JR_ID , JobRole ) VALUES (3,'Manager') 



 SELECT
   JobRole,DT.DocText_ID,
   SUBSTRING(DT.DocText, CHARINDEX(JobRole, DT.DocText)-125, 250) as SubText
FROM
   @Document_Text DT CROSS JOIN  @JobRole JR 
WHERE
   DT.DocText like '%'+ JobRole +'%''

I included table variables so as you can see my thinking. Is this what you were looking for. It avoids cursors but I don't know how well it would scale.

A buddy of mine suggested the same... BUT HE DID NOT PROVIDE THE FULL EXAMPLE! So u dah mahn! MY 'newbie-ism' does not allow me to give a thumbs up on your solution.

Collectives™ on Stack Overflow

Need to use a variable in an SQL Server string function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related