Solved

SQL Query Advice

Posted on 2009-07-06
10
243 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?
0
Comment
Question by:Jeff S
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 100 total points
Comment Utility
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
 
LVL 7

Author Comment

by:Jeff S
Comment Utility
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
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 100 total points
Comment Utility
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
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 100 total points
Comment Utility
In SQL you can also try something like this, discarding the isnull
select .... left(ic.ListName, 25) + space(25 - len(ic.ListName)) .... from ....
 
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 100 total points
Comment Utility
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
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Another possibility
select isnull(left(ic.ListName, 25) + space(25 - len(ic.ListName)), space(25)) .... from ....

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
ralmada fixed it
0
 
LVL 5

Assisted Solution

by:DBDevl
DBDevl earned 100 total points
Comment Utility
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
 
LVL 7

Author Closing Comment

by:Jeff S
Comment Utility
Great tips! Split points to be fair as all helped
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now