Function to find character in string

17 Feb 2006 12:13 AM

Kayda

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

17 Feb 2006 12:29 AM

Roy Harvey

declare @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

Show quote

On 16 Feb 2006 16:13:03 -0800, "Kayda" <blair***@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

17 Feb 2006 12:35 AM

Willson Santoso

The equivalent of instr in MS SQL is CHARINDEX(stringtofind,thewholestring)
with the parameter reversed from that of instr.

Regards,
Willson

Show quote

"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
>
>