asked on

SQL Query Advice

I need help on a SQL Query. I have 3 specific columns that will be merged into one string in Crystal Reports and they have to be a total length (between the 3) = 75 characters.

My specific coding on these 3 fields:

ISNULL(LEFT(ic.ListName, 25), 'xxxxxxxxxxxxxxxxxxxxxxxxx') AS [Primary Payer] ,
ISNULL(LEFT(ic2.ListName, 25), 'xxxxxxxxxxxxxxxxxxxxxxxxx') AS [Secondary Payer] ,
ISNULL(LEFT(ic3.ListName, 25), 'xxxxxxxxxxxxxxxxxxxxxxxxx') AS [Third Payer]

My issue:

I had a [Primary Payer] = 'Zales' and a [Secondary Payer] = 'Aetna Life and Casualty' and a [Third Payer] = 'United Health Care SLCit'.

Is it possible to take the LEFT 25 characters and if not equal to a total of 25 characters pad it with blank spaces?

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff S

ASKER

I get this back:

Msg 245, Level 16, State 1, Line 50
Syntax error converting the varchar value 'Universal Life' to a column of data type int.

SOLUTION

pssandhu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pssandhu

The ISNULL function in crystal returns either true if the field is null and false if the field is not, just so we all are clear on that.
P.

SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

UnifiedIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

Another possibility

select isnull(left(ic.ListName, 25) + space(25 - len(ic.ListName)), space(25)) .... from ....

Open in new window

momi_sabag

ralmada fixed it

SOLUTION

DBDevl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff S

ASKER

Great tips! Split points to be fair as all helped