April 17, 2008 at 12:06 pm
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.
April 17, 2008 at 1:12 pm
Yes, It is possible to select space between two words.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 17, 2008 at 1:16 pm
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?
April 17, 2008 at 2:26 pm
ok... great. that gives me a starting point.
appreciate the feedback.
April 18, 2008 at 12:21 am
Use the following script -
DECLARE @StringVARCHAR(255),
@SearchStrVARCHAR(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.
April 18, 2008 at 12:25 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply