CODEWITHSUNDEEP
sql-serverssmsssms-2012
sigil
sigil

Reputation: 9546

How to search for a string/substring in SSMS results?

This seems pretty straightforward, but for some reason I'm not finding anything about how to do it. I'm using SQL Server Management Studio 2012, and I have a set of results returned from a SELECT query, say, select a,b from x. How can I search in column b for a substring? In Access I would click on the column and type Ctrl+F, but in SSMS that seems to only be used for searching the SQL itself, not the results. How can I search in my results? I know I can modify my query to only return that result, e.g.:

select a,b from x where b like '*hello*'

but I want to get all the rows returned, not just that one.

UPDATE: The responses I'm getting are about how to build a query that selects only the rows I'm looking for, which, as I specified above, is not what I'm looking for. I want all rows returned, and I want to look around in the search results in the SSMS interface to find the desired values. The reason is that I want to see these values in the context of other rows that don't have them.

Upvotes: 3

Views: 14532

Answers (5)

JoshInDC
JoshInDC

Reputation: 31

Just spit the snippet of text out in an extra column where the term is first found in the larger string. To do this, look at this example:

declare @FindThis varchar(100)

set @FindThis = 'California'

select ID, description, SUBSTRING(description, charindex(@FindThis , description)-40,80) as ItIsFoundHere
     FROM [myTable]
     where description like '%' + @FindThis + '%'

Upvotes: 0

Andrei Rantsevich
Andrei Rantsevich

Reputation: 2935

There is no native SSMS functionality to search through results grid.

That is why I have added this function into my add-in (SSMSBoost): it allows to search through results grid using wildcards. There are some other add-ins allowing to do it as well.

Upvotes: 3

Dave Mason
Dave Mason

Reputation: 4936

In SQL Server Management Studio, you can output your query results to text (CTRL + T), re-run your query, click in the results pane, and CTRL + F to find strings from an unfiltered query. To revert query results to grid, hit CTRL + D.

Upvotes: 18

Dave C
Dave C

Reputation: 7392

Use:

select a,b from x where b like '%hello%'

Or:

select a,b from x where charindex('hello',b)>0

CHARINDEX is far faster than LIKE.

Upvotes: 0

John
John

Reputation: 186

select a, substring(b, 2, 3) as b from x

Upvotes: 0

Related Questions