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?
Jeff SAsked:
Who is Participating?
momi_sabagConnect With a Mentor Commented:
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))
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.
pssandhuConnect With a Mentor Commented:
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.
ralmadaConnect With a Mentor Commented:
In SQL you can also try something like this, discarding the isnull
select .... left(ic.ListName, 25) + space(25 - len(ic.ListName)) .... from ....
UnifiedISConnect With a Mentor Commented:
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))
Another possibility
select isnull(left(ic.ListName, 25) + space(25 - len(ic.ListName)), space(25)) .... from ....

Open in new window

ralmada fixed it
DBDevlConnect With a Mentor Commented:
This works too

SELECT CASE WHEN LEN(ic.ListName) < 25
THEN ic.ListName + REPLACE(SPACE(25 - LEN(ic.ListName)), ' ', 'X')
ELSE ic.ListName END
Jeff SAuthor Commented:
Great tips! Split points to be fair as all helped
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.