October 22, 2009 at 5:03 pm
I am using left function on a string column
Sample data :
col1
111-1
122-33
12446
fh-6
fg-6
dh-0
fge-56
I want to select the first 3 characters or the characters before '-' in this column
result should be
111
122
124
fh
dh
fge
Thanks
October 22, 2009 at 5:39 pm
You need to use the charindex function together with the left function, as per:
select
left(Vcr1,
(case
when charindex('-', Vcr1) > 3 or charindex('-', Vcr1) = 0 then 3
else charindex('-', Vcr1) - 1
end))
from Test1
October 23, 2009 at 12:03 am
You could also do this:
SELECT LEFT(YourString, (CHARINDEX('-', YourString)-1))FROM dbo.YourTable
This wouldn't require alteration should the position of your delim character ever move to the 5th position or further.
October 23, 2009 at 5:51 am
diamondgm (10/23/2009)
You could also do this:
SELECT LEFT(YourString, (CHARINDEX('-', YourString)-1))FROM dbo.YourTable
This wouldn't require alteration should the position of your delim character ever move to the 5th position or further.
Well, your solution wouldn't satisfy the condition that it return the first three characters should the "-" not exist. In fact, if there is no "-", then your query would actually fail:
SELECT LEFT('ABCDEFG', (CHARINDEX('-', 'ABCDEFG')-1))
Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.
Since there is no "-", the CHARINDEX() function returns -1, and -1 is an invalid parameter to pass to the LEFT function.
October 23, 2009 at 6:22 am
I think thats different level of validation that you may want to put depending on your business needs/justification,
Declare @STR varchar(10)
SET @STR = 'ABCDEFG'
SELECT LEFT (@str, CASE WHEN CHARINDEX('-', @STR) =0
THEN 3
ELSE (CHARINDEX('-', @STR)-1) END)
---------------------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply