Solved

SQL Query Advice

Posted on 2009-07-06
10
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 100 total points
ID: 24787985
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
ID: 24788030
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
ID: 24788081
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 17

Expert Comment

by:pssandhu
ID: 24788096
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
ID: 24788125
In SQL you can also try something like this, discarding the isnull
select .... left(ic.ListName, 25) + space(25 - len(ic.ListName)) .... from ....
 
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 100 total points
ID: 24788328
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
ID: 24788342
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
ID: 24788557
ralmada fixed it
0
 
LVL 5

Assisted Solution

by:DBDevl
DBDevl earned 100 total points
ID: 24789153
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
ID: 31600294
Great tips! Split points to be fair as all helped
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

749 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