Exporting data with prefixes......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Exporting data with prefixes......

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-08-23 : 06:58:08

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 07:18:00

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

E 12°55'05.25"
N 56°04'39.16"

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-08-23 : 08:37:47

Peso

Thanks for the post, but this has not worked, it is still inputting the prefix code into NULL fields.

If the field has nothing in it I do not want a prefix code inserting in.

This might not be possible to do.

Thanks anyway.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 08:50:24

[code]SELECT
ISNULL('001' + Name, ''),
ISNULL('002' + Address, ''),
ISNULL('003' + Location, '')
FROM
TableName[/code]

KH
[spoiler]Time is always against us[/spoiler]

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-08-23 : 09:29:48

khtan

Thanks for the post, again this didn't work, it still puts in the numeric prefix on NULL fields.

I only want the prefix to be attached to fields with data in, else I want the fields left null (with no prefix).

I might have to use some variables for this.

Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 09:31:42

post the sample data that of such scenario and the expected result

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 10:20:20

looks like your 1st record 10066022 has column with empty string ''

SELECT 	DISTINCT
	ISNULL('000' + NULLIF(reference, ''), '')	AS [Stu Ref],
	ISNULL('051' + NULLIF(address, ''), '')		AS [Address],
	ISNULL('052' + NULLIF(town, ''), '')		AS [Town],
	ISNULL('053' + NULLIF(county, ''), '')		AS [County]
FROM 	person

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 10:22:39

bendertez, what happen to your post with sample data ?

KH
[spoiler]Time is always against us[/spoiler]

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-08-23 : 10:27:59

Sorry i'll do that again.......

select distinct
isnull('000'+reference, '')"Stu Ref",
isnull('051'+address, '')"Address",
isnull('052'+town, '')"Town",
isnull('053'+county, '')"County"
from person

Results:
StuRef.....Address.......Town..........County
66022......051................052................053
66424......051PARSONB....052ASPATRI.....053CUMBRIA
67904......051CRAGGIE....052CARNFOR.....053LANCS
68895......051PENRITH....052CUMBRIA.....053
69283......051STANWIX....052CARLISL.....053CUMBRIA
69819......051CARLISE....052CUMBRIA.....053
70075......051WETHERI....052PENRITH.....053CUMBRIA
70735......051WESTWAR....052WIGTOND.....053CUMBRIA
71266......051CARLISL....052CUMBRIA.....053

Anywhere where there is just a prefix code (where it has been a NULL field) I want leaving blank with no prefix code.

Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 10:30:28

try the last query that i posted

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 10:30:50

SELECT
CASE WHEN Name IS NULL THEN '' ELSE '001' + Name END AS [Name],
CASE WHEN Address IS NULL THEN '' ELSE '002' + Address END AS [Address],
CASE WHEN Location IS NULL THEN '' ELSE '003' + Location END AS [Location]
FROM TableName

E 12°55'05.25"
N 56°04'39.16"

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-08-23 : 12:00:06

Peso's fix seem to do the trick

Thank you experts.....!!

The only thing I have wrong with it now is that the address field is is in a notes format so you have to strip out the relevant parts of it

CASE WHEN (substr(add1.a_name,1,dbo.instr(add1.a_name+char(13) + char(10),char(13) + char(10),1,1)-1) IS NULL THEN '' ELSE '000' + (substr(add1.a_name,1,dbo.instr(add1.a_name+char(13) + char(10),char(13) + char(10),1,1)-1) END AS [Address1]

Doing the CASE WHEN fix with this substring query throws up the error:

Incorrect syntax near the keyword 'THEN'

I have to do the substring query to get the individual address fields out.....its a terrible database i'm working with...!!

Any ideas?

Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 12:53:09

Use the dbo.fnParseString found here at SQLTeam.

SELECT
CASE WHEN Name IS NULL THEN '' ELSE '001' + Name END AS [Name],
CASE WHEN Address IS NULL THEN '' ELSE '002' + dbo.fnParseString(-1, CHAR(13), Address) END AS [Address],
CASE WHEN Location IS NULL THEN '' ELSE '003' + Location END AS [Location]
FROM TableName

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources