Networks

Ask something
Question Locked

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

0 helpful
4 replies

by dcleary 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

4 replies

by msi77

Try this

INSERT INTO newTable
SELECT
'001'+Name,
'002'+Address,
'003'+Location
FROM
TableName
WHERE Name+Address+Location IS NOT NULL

SQL Exercises

by athibaud

"...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, '')

by msi77

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

This discussion is closed to new replies.