November 17, 2009 at 9:56 am
Hi
Can anyone advise me if it is possible to run a SQL query when one of the string values contain 2 spaces within the string itself. If not how can I work around this (please see below):
order_branchcode = 'XXXXXX XX'
Thanks in advance.
Sean
November 17, 2009 at 10:12 am
Yes, it can.
But I'm not clear on what exactly you are trying to do here. Are you trying to select all the rows that contain two spaces? If so, then you can use Like or CharIndex or PatIndex to find those.
- 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
November 17, 2009 at 10:20 am
My dilemma is that the query has to have the ability to cope with either one of three formats:
1) a normal 10 chracter string 'xxxxxxxxxx'
2) a 10 character string containing a single space 'xxxxxx xxx'
2) a 10 character string containing a two spaces 'xxxxxx xx'
Regards
Sean
November 17, 2009 at 10:44 am
What does "cope with" mean in this context?
Do you mean you want each of those combinations to be handled differently? If so, how?
- 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
November 17, 2009 at 9:30 pm
Are you looking for something like this?
Declare @sTest varchar(10)
Set @sTest='xxxxxxxxxx'
SELECT
CASE WHEN Len(Replace(@sTest,' ',''))=10 THEN 'CASE1'
WHEN Len(Replace(@sTest,' ',''))=9 THEN 'CASE2'
WHEN Len(Replace(@sTest,' ',''))=8 THEN 'CASE3'
END As [Result]
Set @sTest='xxxxxx xxx'
SELECT
CASE WHEN Len(Replace(@sTest,' ',''))=10 THEN 'CASE1'
WHEN Len(Replace(@sTest,' ',''))=9 THEN 'CASE2'
WHEN Len(Replace(@sTest,' ',''))=8 THEN 'CASE3'
END As [Result]
Set @sTest='xxxxxx xx'
SELECT
CASE WHEN Len(Replace(@sTest,' ',''))=10 THEN 'CASE1'
WHEN Len(Replace(@sTest,' ',''))=9 THEN 'CASE2'
WHEN Len(Replace(@sTest,' ',''))=8 THEN 'CASE3'
END As [Result]
November 17, 2009 at 10:53 pm
Hi,
Do you mean that your query should work(give the same result) even if the string contains white spaces in it ??
If so then you can do this :
declare @order_branchcode varchar(50)
set @order_branchcode = 'XXXXXXXX XX'
while(charindex(' ',@order_branchcode) >0)
begin
set @order_branchcode = replace(@order_branchcode,' ' ,'')
end
select @order_branchcode
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply