Comma Separator Delimiter

Question

Post reply

Comma Separator Delimiter

farooq.hbs

SSCrazy

Points: 2083
More actions
May 14, 2014 at 7:47 am

#293300

Hi Guys,
I am struck with some fires logic, which i am not able to figure out 🙂
I have a comma separator field,
For Example, the field values is
Annual Holiday, Annual Leave - David Hornby,
Solution: I want to retrieve the first comma string (Annual Holiday) and remove the rest value from the field and display.
It should be Just Annual Holiday

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

farooq.hbs SSCrazy Points: 2083 More actions · Answer 1

I found the solution..

LEFT([field], CHARINDEX(',', [field]) - 1)

:):):)

Thanks:-):-):-):-)

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 2

farooq.hbs (5/14/2014)
I found the solution..
LEFT([field], CHARINDEX(',', [field]) - 1)
:):):)
Thanks:-):-):-):-)

That will work if you are certain that your source string will ALWAYS contain at least one comma. If you cannot guarantee that then you will need to test for the presence of a comma and return the original string if there is none.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

crmitchell (5/15/2014)
farooq.hbs (5/14/2014)
I found the solution..
LEFT([field], CHARINDEX(',', [field]) - 1)
:):):)
Thanks:-):-):-):-)
That will work if you are certain that your source string will ALWAYS contain at least one comma. If you cannot guarantee that then you will need to test for the presence of a comma and return the original string if there is none.

There's no need for a great validation process, just append a comma to the field.

LEFT([field], CHARINDEX(',', [field] + ',') - 1)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Luis Cazares (5/15/2014)
crmitchell (5/15/2014)
farooq.hbs (5/14/2014)
I found the solution..
LEFT([field], CHARINDEX(',', [field]) - 1)
:):):)
Thanks:-):-):-):-)
That will work if you are certain that your source string will ALWAYS contain at least one comma. If you cannot guarantee that then you will need to test for the presence of a comma and return the original string if there is none.
There's no need for a great validation process, just append a comma to the field.
LEFT([field], CHARINDEX(',', [field] + ',') - 1)

Careful now... concatenation is what killed the cat on some of the earlier splitters. It will probably be faster to do the ISNULL/NULL IF thing to change a 0 to 8000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Jeff Moden (5/15/2014)
Luis Cazares (5/15/2014)
crmitchell (5/15/2014)
farooq.hbs (5/14/2014)
I found the solution..
LEFT([field], CHARINDEX(',', [field]) - 1)
:):):)
Thanks:-):-):-):-)
That will work if you are certain that your source string will ALWAYS contain at least one comma. If you cannot guarantee that then you will need to test for the presence of a comma and return the original string if there is none.
There's no need for a great validation process, just append a comma to the field.
LEFT([field], CHARINDEX(',', [field] + ',') - 1)
Careful now... concatenation is what killed the cat on some of the earlier splitters. It will probably be faster to do the ISNULL/NULL IF thing to change a 0 to 8000.

It's good to know. I usually don't work with strings that long. I wouldn't expect this scenario to be one of those cases.