SQL Question

[lauriebizz]

I though maybe someone could answer my question... typically I use Crystal 9, but have a SQL question...

the field claimant_name on claim table has last-name*first (example: COOPER*LAV). Do you know how to get the last name only out of that cell (the part to the left of the *)?

Thanks for any help!
Laurie

 

[synapsevampire]

Laurie, you need to post your database type.
SQL isn't a generic language that covers all databases.

Your question doesn't seem SQLlimited anyway, so I suggest that you NOT designate architecture, rather state what you have (show data) and what you need (output).

Try:

if ubound(split({table.field},"*")) > 1 then
split({table.field},"*")[1]
else
{table.field}

-k

 

[lauriebizz]

Thanks, I ended up using:

SELECT

CLAIMANT_NAME

,SUBSTR(CLAIMANT_NAME,1,INDEX(CLAIMANT_NAME,'*')-1) as Last_Name

FROM DB.TABLE

WHERE CLAIMANT_NAME LIKE '%*%';

 

[synapsevampire]

Okey dokey...

Your solution is basically the same thing, however you're pulling back 2 fields and yours will only pull back data if there's an asterisk in the claimant field, whereas my solution relies on Crystal for part of the processing. and always returns a value.

-k