July 31, 2007 at 3:15 pm
Hello -
Is there any way to capture all the text LEFT of the CHARINDEX character in a text string, dropping everything on the right?
I've been reading through everything for days and days. If it's not possible, could anyone please explain why it is not possible in understandable format for newbie?
Thanks,
July 31, 2007 at 3:25 pm
There are a number of methods you could use to do this including, but necessarily limited to, the following example. All three of these methods are well documented in BOL. Where have you been reading for days and days? Look up 'String Functions' in BOL for all functions that work with string values.
DECLARE @Table TABLE (RowNum int IDENTITY(1,1), Value varchar(200))
INSERT INTO @Table (Value)
SELECT 'This is a substring test'
SELECT 'SUBSTRING: ' as Method, SUBSTRING(Value,1,CHARINDEX('substring', Value)-1)
FROM @Table
UNION ALL
SELECT 'LEFT: ', LEFT(Value,CHARINDEX('substring', Value)-1)
FROM @Table
UNION ALL
SELECT 'STUFF: ', STUFF(Value,CHARINDEX('substring', Value),LEN(Value),'')
FROM @Table
July 31, 2007 at 3:55 pm
John,
to avoid errors when pattern cannot be found in string use this:
CASE WHEN CHARINDEX('substring', Value) = 0
THEN Value
ELSE LEFT(Value,CHARINDEX('substring', Value)-1)
END
_____________
Code for TallyGenerator
July 31, 2007 at 4:00 pm
July 31, 2007 at 4:09 pm
Thanks, Sergi and John -
I'll try this with my data.
Kenena
July 31, 2007 at 5:08 pm
Thanks for your help, but I am receiving the error message that data type of TEXT is invalid for the function
The comments field is like the following:
TOTAL USAGE READING NEEDED FOR PM GENERATION "SYSTEM CHECKS,INSPECTIONS and OPERATION" *Engine undergoing 40,000hour in frame overhaul. I can change the * to another character, but I would like to first know I can get ONLY what is left of the *. Any help would be appreciated. Also Sergi - where would the CASE statement go? Thanks,Kenena |
July 31, 2007 at 7:21 pm
For text datatype you need to use PATHINDEX instead of CHARINDEX and SUBSTRING instead of LEFT.
Check Books Online for the syntax.
CASE goes right after SELECT.
_____________
Code for TallyGenerator
August 1, 2007 at 6:23 am
The use of
SUBSTRING([textcolumn],1,PATINDEX('%*%',[textcolumn]))
will not produce an error if * is not in the data but would produce erronous data depending on your view of 'erroneous'
If an empty string is required then the CASE is not required but if the whole data is required then the CASE (as in Sergiy's answer) is required
Far away is close at hand in the images of elsewhere.
Anon.
August 1, 2007 at 6:26 am
p.s. the above in SQL2K will return a maximum of 8000 chars (the limit for varchar)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply