Function to find character in string

Hi:
I was looking through BOL for a function that would return the
numerical place where a string first occurs (similar to Crystal
Report's 'instr' function):
so
FUNKYFUNCTION('abcdefgd','d',1)
would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
argument is the starting place in the string.
Basically, what I want to do is trim everything after the first space
is found. I might have certain field values that end up as:
5445 UNWANTEDSTRINGETXT
I want this field to be changed to '5445' when it finds unwanted text.
So having this function would make this easy. Or maybe there is another
way in the absence of such a function?
Thanks for the help,
Kaydadeclare @.str varchar(80)
set @.str = '5445 UNWANTEDSTRINGETXT'
SELECT CHARINDEX(' ', @.str),
CASE WHEN CHARINDEX(' ', @.str) = 0
THEN @.str
ELSE SUBSTRING(@.str,1,CHARINDEX(' ', @.str)-1)
END
On 16 Feb 2006 16:13:03 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>I was looking through BOL for a function that would return the
>numerical place where a string first occurs (similar to Crystal
>Report's 'instr' function):
>so
>FUNKYFUNCTION('abcdefgd','d',1)
>would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
>argument is the starting place in the string.
>Basically, what I want to do is trim everything after the first space
>is found. I might have certain field values that end up as:
>5445 UNWANTEDSTRINGETXT
>I want this field to be changed to '5445' when it finds unwanted text.
>So having this function would make this easy. Or maybe there is another
>way in the absence of such a function?
>Thanks for the help,
>Kayda|||The equivalent of instr in MS SQL is CHARINDEX(stringtofind,thewholestring)
with the parameter reversed from that of instr.
Regards,
Willson
"Kayda" wrote:

> Hi:
> I was looking through BOL for a function that would return the
> numerical place where a string first occurs (similar to Crystal
> Report's 'instr' function):
> so
> FUNKYFUNCTION('abcdefgd','d',1)
> would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
> argument is the starting place in the string.
> Basically, what I want to do is trim everything after the first space
> is found. I might have certain field values that end up as:
> 5445 UNWANTEDSTRINGETXT
> I want this field to be changed to '5445' when it finds unwanted text.
> So having this function would make this easy. Or maybe there is another
> way in the absence of such a function?
> Thanks for the help,
> Kayda
>