[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to replace empty column with data in sql server 2012

Posted on 2014-08-26
11
Medium Priority
?
240 Views
Last Modified: 2014-08-26
Hi

I have a query attached with output also and i'm looking for a way to replace the empty columns in column(Barcode) with data (maybe something like '-')
SELECT Seq,FldJobKey,FldStationNmKey,StaDesc AS FldStationDesc,JobdStationIdKey,JobdJobKey
              ,([General].[ParseLotInst](RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70))))) AS Barcode
              ,JobDInstruction AS WorkcentreInstructions FROM
              (SELECT
              CONVERT(INT,CONVERT(VARCHAR,FLDSEQ1KEY) + CONVERT(VARCHAR,FLDSEQ2KEY) + CONVERT(VARCHAR,FLDSEQ3KEY) + CONVERT(VARCHAR,FLDSEQ4KEY) + CONVERT(VARCHAR,FLDSEQ5KEY) + CONVERT(VARCHAR,FLDSEQ6KEY)) SEQ
              ,FldJobKey
              ,FldStationNmKey
              FROM TblFlowDetail
              WHERE FldJobKey='PNOE143303' AND FldStationNmKey='SMT1') AS FD
                     LEFT JOIN (SELECT JobdJobKey
									, JobdStationIDKey
									, RTRIM(SUBSTRING(JobdInstruction, 0, 70)) AS JobdInstruction
									, JobdRecIdKey
								FROM TblJobDetail
								WHERE JobdJobKey ='PNOE143303'
									  AND JobdStationIDKey = 'SMT1'
									  AND JobDInstruction IS NOT NULL
									  AND RTRIM(SUBSTRING(JobdInstruction, 0, 70)) <> '') AS INS
                                                ON FD.FldJobKey=INS.JobdJobKey AND FD.FldStationNmKey= INS.JobdStationIdKey
                                                INNER JOIN TblStation AS STA 
                                                ON FD.FldStationNmKey = STA.StaIdKey
       WHERE SEQ >= 1

Open in new window

empty-barcode-column.docx

Thanks
0
Comment
Question by:razza_b
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 1000 total points
ID: 40285146
Do you mean if the field is empty then you would prefer to show '-' in the results?
If so then:
isnull(([General].[ParseLotInst](RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70))))),'-')  AS Barcode

Open in new window

0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1000 total points
ID: 40285147
It depends if it is blank or null.

I'd do something like this:

isnull(nullif((RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70)))), ''), '-') AS Barcode

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 40285153
thanks for quick response guys. The empty column is blank.

QPR - i tried your example and i get the same output.
Lee - i dont get the '-', but whati get is the empty column being filled up with its instructions instead, so column barcode has all the same values as instruction.

Cheers
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 29

Expert Comment

by:QPR
ID: 40285158
Try
replace(([General].[ParseLotInst](RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70))))),'','-') AS Barcode

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 40285163
guys i used this and works...

isnull(nullif([General].[ParseLotInst](RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70)))), ''), '-') AS Barcode

Lee think you just had the [General].[ParseLotInst] missing and QPR you had teh nullif missing, but 2 of you were great help!!
0
 
LVL 1

Author Comment

by:razza_b
ID: 40285164
sorry i tried to share the points there, how can i change?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40285166
Does your query above work?

Because between [General].[ParseLotInst] and the barcode bit, there is no comma separating them.

SELECT Seq,FldJobKey,FldStationNmKey,StaDesc AS FldStationDesc,JobdStationIdKey,JobdJobKey
              ,([General].[ParseLotInst](RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70))))) AS Barcode
              ,JobDInstruction AS WorkcentreInstructions FROM

I'd need to see some example data and a comparison of what you get to what you expect.

Using replace() on an empty string doesn't work. That's why I converted to null first and then used isnull to replace it with a hyphen.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40285169
If you wish to change the points allocation, you can request attention on the accepted answer and ask a moderator to undo the acceptance to allow you to redo it.
0
 
LVL 1

Author Comment

by:razza_b
ID: 40285175
i used this and that got the hyphen in there...

SELECT Seq,FldJobKey,FldStationNmKey,StaDesc AS FldStationDesc,JobdStationIdKey,JobdJobKey
              ,isnull(nullif([General].[ParseLotInst](RTRIM(LTRIM(SUBSTRING(JobdInstruction, 0, 70)))), ''), '-') AS Barcode
              ,JobDInstruction AS WorkcentreInstructions FROM
              (SELECT
              CONVERT(INT,CONVERT(VARCHAR,FLDSEQ1KEY) + CONVERT(VARCHAR,FLDSEQ2KEY) + CONVERT(VARCHAR,FLDSEQ3KEY) + CONVERT(VARCHAR,FLDSEQ4KEY) + CONVERT(VARCHAR,FLDSEQ5KEY) + CONVERT(VARCHAR,FLDSEQ6KEY)) SEQ
              ,FldJobKey
              ,FldStationNmKey
              FROM TblFlowDetail
              WHERE FldJobKey='PNOE143303' AND FldStationNmKey='SMT1') AS FD
                     LEFT JOIN (SELECT JobdJobKey
                                                      , JobdStationIDKey
                                                      , RTRIM(SUBSTRING(JobdInstruction, 0, 70)) AS JobdInstruction
                                                      , JobdRecIdKey
                                                FROM TblJobDetail
                                                WHERE JobdJobKey ='PNOE143303'
                                                        AND JobdStationIDKey = 'SMT1'
                                                        AND JobDInstruction IS NOT NULL
                                                        AND RTRIM(SUBSTRING(JobdInstruction, 0, 70)) <> '') AS INS
                                                ON FD.FldJobKey=INS.JobdJobKey AND FD.FldStationNmKey= INS.JobdStationIdKey
                                                INNER JOIN TblStation AS STA
                                                ON FD.FldStationNmKey = STA.StaIdKey
       WHERE SEQ >= 1
0
 
LVL 1

Author Comment

by:razza_b
ID: 40285179
i have put in request...
0

Featured Post

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.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 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