searching for Space in a string

Question

Post reply

searching for Space in a string

william_crawley

SSC Veteran

Points: 293
More actions
December 29, 2010 at 7:34 am

#244814

Hi All,
declare@posStart int,
@BuildStr varchar(max),
@OccupationFilter varchar(50)
set @OccupationFilter = 'houses public'
set @posStart = 1
set @BuildStr = ''
Select charindex(@OccupationFilter,char(32),1)
The above snippet is part of a wider SP, I'm trying to obtain the position of the white space in the @OccupationFilter variable. I am expecting the charindex value to return 7, but it always returns 0.
If I do:
charindex(@OccupationFilter,' ',1)
it also returns 0.
strange also if I do charindex(@OccupationFilter,'p',1) or any other character in my variable, it always returns 0 ?????

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

Here you go.

declare @posStart int,

@BuildStr varchar(max),

@OccupationFilter varchar(50)

set @OccupationFilter = 'houses public'

set @posStart = 1

set @BuildStr = ''

select PATINDEX('% %',@OccupationFilter)

MCITP SQL 2005, MCSA SQL 2012

GSquared SSC Guru Points: 260824 More actions · Answer 2

You're doing Charindex backwards. The string you're looking for goes first, the string you're looking in goes second. Essentially, you're trying to find "houses public" in " ". Reverse the two, should fix it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

william_crawley SSC Veteran Points: 293 More actions · Answer 3

Thanks Guy's I'm nearly there.

So my BuildStr now has the Value :

and Name like '%houses%' and Name like '%public%'

When I append this to the following Statement:

select CCOccupationID,Code,

Name,

ABIClass,

RiskClassID

fromCCOccupations

wherertrim(ABIClass)like '%' + rtrim(isnull(null, ABIClass)) + '%' + @BuildStr

I do not receive any results back in my query. If I Write the Select statement out longhand, I recieve valid data back from my query

p.s. Dont worry about rtrim(isnull(null, ABIClass)) I've hard coded null for now, but this will simply be another variable passed in

WayneS SSC Guru Points: 95460 More actions · Answer 4

You'll have to use dynamic sql to run it that way.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2