select between left & right...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » select between left & right...

select between left & right...

Rate Topic

Display Mode

Topic Options

Author

Message

b4better

Posted Thursday, April 17, 2008 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 25, 2008 7:37 AM
Points: 13, Visits: 52

lets say i have a varchar column such as the following:

The quick brown fox jumps over the lazy dog

i guess using string functions would it be possible to select a space
between 2 words?

for example i just need ' fox ' and not start or the finish of the row.
in fact, in this case to be more precise my column is the same across
all rows, but ' fox ' could be ' tiger ' or ' elephant ' or ' bird ' etc.

so just selecting between 'the quick brown ' and ' jumps over the lazy dog.

ideas?

thanks in advance.

Post #486680

Mohan Kumar

Posted Thursday, April 17, 2008 1:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 03, 2013 4:44 PM
Points: 1,336, Visits: 595

Yes, It is possible to select space between two words.

--www.sqlvillage.com

Post #486737

Matt Miller (#4)

Posted Thursday, April 17, 2008 1:16 PM

SSCertifiable

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:56 PM
Points: 6,997, Visits: 13,941

I'd look at setting up a CLR function using Regular Expressions. You haven't given me enough to understand why ' fox ' and not any one of the other words; that being said - CLR is in my opinion a far superior string handler to what we have in T-SQL native.

Regex.Match(n) or Regex.Matches could easily return either the nth instance of the pattern, or ALL of the instances of the pattern. Lots of built-in power - might be worth tapping into.

I suspect that you'd get a LOT of mileage out of it. And the VS team at Microsoft spent a lot of time optimizing regular expression performance, so your T-SQL shouldn't really suffer from using it (you may find it's actually faster than trying it with the built-in functions)...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #486741

b4better

Posted Thursday, April 17, 2008 2:26 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 25, 2008 7:37 AM
Points: 13, Visits: 52

ok... great. that gives me a starting point.

appreciate the feedback.

Post #486785

Chandrachurh Ghosh

Posted Friday, April 18, 2008 12:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 18, 2012 12:50 AM
Points: 174, Visits: 315

Use the following script -

DECLARE @String VARCHAR(255),
@SearchStr VARCHAR(255)
SET @String='The quick brown fox jumps over the lazy dog'
SET @SearchStr='fox '+'////'
SELECT SUBSTRING(@String,CHARINDEX(LEFT(@SearchStr,LEN(@SearchStr)-4),@String),LEN(@SearchStr)-4)

Note: You can directly have the column name in place of the variable.

Chandrachurh Ghosh
DBA – MS SQL Server
INFOSYS Limited
Quality is not an act, it is a habit.

Post #486917

Chandrachurh Ghosh

Posted Friday, April 18, 2008 12:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 18, 2012 12:50 AM
Points: 174, Visits: 315

You will not have any problem with prefix blanks, as the LEN function only trims the trailing blanks.....hope this helps

Chandrachurh Ghosh
DBA – MS SQL Server
INFOSYS Limited
Quality is not an act, it is a habit.

Post #486920

« Prev Topic | Next Topic »

Permissions