May 12, 2008 at 9:02 am
Hi All,
Can you help me with this, I'm wanting to find the '-' value in a field and return everything to the left of this value, so for example if I have 0845-General, then I want 0845 returning, or if I have 020-London, then I want 020 returning only.
I use an InSTR in MS Access but cannot find anything like this in SQL.
Anyone offer me a hand? Thanks in advance:D
May 12, 2008 at 9:18 am
select LEFT(Column1,CHARINDEX('-',Column1)- 1)
from Table1
You can see all string functions by searching for "String Functions" in BooksOnLine.
Greg
May 12, 2008 at 1:31 pm
Hi All,
Okay, I tried the suggested, and after reading books online, looks like I'm close but I h=still can't get it to work. My Code is
SELECT LEFT(Q5,CHARINDEX('-',Q5)- 1) As Test
FROM cmp_OrangeBroadband
WHERE LastCRC = 'Sale'
And the error message I get is:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Now some of these columns do have nulls in as well, which I will replace with '' i.e.
SELECT ISNULL(LEFT(Q5,CHARINDEX('-',Q5)- 1),'') As Test
FROM cmp_OrangeBroadband
WHERE LastCRC = 'Sale'
What am I missing?
May 12, 2008 at 1:38 pm
Add:
and CHARINDEX('-',Q5) > 0
to your Where clause.
See if that gives you what you need.
- 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
May 12, 2008 at 1:42 pm
Yep, worked fine! Code Below... Why is this then, why was this code needed on the where clause...? :ermm:
SELECT ISNULL(LEFT(Q5,CHARINDEX('-',Q5)- 1),'') As Test
FROM cmp_OrangeBroadband
WHERE LastCRC = 'Sale' and CHARINDEX('-',Q5) > 0
May 12, 2008 at 1:49 pm
Ah, but I still need the Nulls to be displayed, so if Q5 has 020-London, then I need 020 showing, If 0845-General, then 0845 needs to be displayed, and likewise if NULL i need '' instead.
May 12, 2008 at 1:58 pm
Without that, it was trying to get the left -1 character of the column. Charindex returns 0 if the character isn't in the string, and then you're subtracting 1 from that and using Left.
Example:
left('1234567890', charindex('a', '1234567890') -1)
First, it finds that the character isn't in the string, so charindex returns 0.
Next, it subtracts 1 from 0, getting -1.
Next, it tries to run the Left command, with a length of -1, and just blows up and gives you an error message.
Now, if you need to deal with values that will have the substring, and values that won't, at the same time, take the charindex line out of the Where clause, and use:
SELECT left(Q5,
isnull(
nullif(charindex('-', Q5, 0) -1, -1),
len(Q5)))
FROM cmp_OrangeBroadband
WHERE LastCRC = 'Sale'
With that in your select statement, it will cut off values that have a hyphen in them, and give you the whole thing for values that don't have a hyphen in them. You can also wrap that in an overall "isnull", if you want, but you don't have to to make the code work.
- 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
May 12, 2008 at 2:08 pm
Thank worked a Treat! Thank you! The code is as below;
SELECT ISNULL(LEFT(Q5, ISNULL(NULLIF(CHARINDEX('-', Q5, 0) -1, -1),LEN(Q5))),'0845')
FROM cmp_OrangeBroadband
WHERE LastCRC = 'Sale'
😀
May 12, 2008 at 2:40 pm
Excellent! Glad it worked for you.
- 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply