how to replace empty column with data in sql server 2012

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
LVL 1
razza_bAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
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
razza_bAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Open in new window

0
razza_bAuthor Commented:
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
razza_bAuthor Commented:
sorry i tried to share the points there, how can i change?
0
Lee SavidgeCommented:
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
Lee SavidgeCommented:
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
razza_bAuthor Commented:
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
razza_bAuthor Commented:
i have put in request...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.