Question

Locked

Exporting fields from SQL server with prefixes........

By dcleary ·
Hello

I'm trying to export some data with prefixed codes against each field. Which will be then imported into another legacy system using the codes as positional pointers.

e.g:
Name.......Address.......Location
001Smith...002High St....003Manchester

This I can do with no probs:

SELECT
'001'+Name,
'002'+Address,
'003'+Location
FROM
TableName

However looking at the overall data export if there are NULL values in the data it correctly displays just the code on its own.

What I really want is that if the field is NULL then do not insert the code.

I only want codes against fields with data in.

Can anybody think of a SQL solution or a way I can get around this.

Thank you

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

IS NOT NULL?

by msi77 In reply to Exporting fields from SQL ...

Try this
<br>
INSERT INTO newTable<br>
SELECT
'001'+Name,
'002'+Address,
'003'+Location<br>
FROM
TableName<br>
WHERE Name+Address+Location IS NOT NULL
<br><br>
<a href="http://www.sql-ex.ru/">SQL Exercises</a>

Collapse -

ISNULL('001' + name, '')

by athibaud In reply to IS NOT NULL?

"...looking at the overall data export if there are NULL values in the data it correctly displays just the code on its own."

No, '001' + NULL --> NULL

The solution proposed by msi77 would loose Address and Location when Name is NULL but the latter aren't.

Try instead:
SELECT ISNULL('001' + name, ''), ...

From your statement I would guess you have empty strings, not NULL fields. If this is the case you can try:

ISNULL(SUBSTRING('001', 1, 3 * SIGN(DATALENGTH(RTRIM(name)))) + name, '')

Collapse -

Any or All

by msi77 In reply to ISNULL('001' + name, '')

I understood this so if ANY of those three columns is null then row should not be inserted.

Back to Networks Forum
4 total posts (Page 1 of 1)  

Hardware Forums