SELECT RIGHT('HELLO WORLD', 3);
SELECT LEFT('HELLO WORLD', 3);
SELECT
CHARINDEX(
' '
,
'Hello World'
);
SUBSTRING
(
'HELLO WORLD'
,4,5)
'JOHNNY BELL'
)
LEFT
,CHARINDEX(
)-1)
Richard Mueller edited Revision 3. Comment: Make headings for TOC
Ed Price - MSFT edited Revision 1. Comment: TOC and tags
Why don't you talk about PATINDEX as well?
Although I understand the concept, it's not really something we use in our environment at work.
Hi Jonny
Using the LEFT and CHARINDEX together is exactly what I need, however, when trying to use it in the following statement I get an error
SELECT LEFT(t.[tda grade],CHARINDEX('.',t.[tda grade])-1)
(Where t.[tda grade] has the value "SEO..CSF National" - all I want returned is "SEO")
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Any ideas?
Hi Richard,
I can't seem to find any issues with the statement when I run "SELECT LEFT('SEO..CSF National',CHARINDEX('.','SEO..CSF National')-1)", so I'm assuming it's something to do with the resultset that the statement is operating on.
Ensure that your statement does include a where statement:
WHERE t.[tda grade] = 'SEO..CSF National'
If the value you're looking for in CHARINDEX does not exist in the string then the value passed to the LEFT function will be invalid, hence the error. Expanding on my answer, it seems you need to return the value "SEO" from that string, so why not just use SELECT LEFT(t.[tda grade],3) WHERE t.[tda grade] = 'SEO..CSF National' or SUBSTRING(t.[tda grade],1,3) WHERE t.[tda grade] = 'SEO..CSF National'??
Thanks for the prompt response Jonny.
The field contains multi values, such as "SCS Band 1..CSF National", which is why I thought this method would be ideal. What you've suggested did make me examine the data, and I've found one value which doesn't have a '.', therefore the result of the CHARINDEX was 0.
By adding the -1 to the expression meant the LEFT function was trying to start at -1, hence the error!
Thanks for your help