Questions

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

+
0 Votes
Locked

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

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
  • +
    0 Votes
    msi77

    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>

    +
    0 Votes
    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, '')

    +
    0 Votes
    msi77

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

  • +
    0 Votes
    msi77

    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>

    +
    0 Votes
    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, '')

    +
    0 Votes
    msi77

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