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?
LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you can try to use the space(x) function
maybe something like:

isnull( LEFT(ic.ListName, 25) + substring(space(25),1, 25-LEFT(ic.ListName, 25)), space(25))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
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.
0
pssandhuCommented:
The syntax you posted is in in SQL. So once you have the filed in crystal you can do something like this:
{Table.PrimaryPayer }+Space(25-Len({Table.Primary Payer }))
this should do it.
P.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pssandhuCommented:
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.
0
ralmadaCommented:
In SQL you can also try something like this, discarding the isnull
select .... left(ic.ListName, 25) + space(25 - len(ic.ListName)) .... from ....
 
0
UnifiedISCommented:
Why not just cast your names as char(25)?
SELECT CAST(ISNULL(ic.ListName, '') AS char(25)) + CAST(ISNULL(ic2.ListName, '') AS char(25)) + CAST(ISNULL(ic3.ListName, '') AS char(25))
0
ralmadaCommented:
Another possibility
select isnull(left(ic.ListName, 25) + space(25 - len(ic.ListName)), space(25)) .... from ....

Open in new window

0
momi_sabagCommented:
ralmada fixed it
0
DBDevlCommented:
This works too

SELECT CASE WHEN LEN(ic.ListName) < 25
THEN ic.ListName + REPLACE(SPACE(25 - LEN(ic.ListName)), ' ', 'X')
ELSE ic.ListName END
0
Jeff SAuthor Commented:
Great tips! Split points to be fair as all helped
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.