Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select between left & right... Expand / Collapse
Author
Message
Posted Thursday, April 17, 2008 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, April 17, 2008 1:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 3, 2013 4:44 PM
Points: 1,336, Visits: 595
Yes, It is possible to select space between two words.

--www.sqlvillage.com
Post #486737
Posted Thursday, April 17, 2008 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 7,105, Visits: 15,444
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
Posted Thursday, April 17, 2008 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, April 18, 2008 12:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 3:44 AM
Points: 179, Visits: 331
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
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Post #486917
Posted Friday, April 18, 2008 12:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 3:44 AM
Points: 179, Visits: 331
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
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Post #486920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse