We help IT Professionals succeed at work.

SQL Query Advice

Medium Priority
272 Views
Last Modified: 2012-05-07
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?
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Another possibility
select isnull(left(ic.ListName, 25) + space(25 - len(ic.ListName)), space(25)) .... from ....

Open in new window

ralmada fixed it
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Great tips! Split points to be fair as all helped
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.