Spliting a Zip +4

[Mike Hughes]

Can someone show me how I can split the Zip+4 in this query, if it can be done at all?

SELECT
RTrim([CLIENT TABLE].CITY)+' , '+RTrim([CLIENT TABLE].ST)+' '+RTrim([CLIENT TABLE].ZIP) AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER, [CLIENT TABLE].PIN,
[CLIENT TABLE].A1, [CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]
FROM [CLIENT TABLE];

Thanks

 

[pdx_man]

I am going to assume you are asking how to get the first 5 characters and the last 4 characters of the zip (if they exist). There are easier solutions, but without knowing what the data type is ...

SELECT LEFT([CLIENT TABLE].ZIP,5) as FirstFive,
IIF(LEN(RTRIM([CLIENT TABLE].ZIP)) > 7, RIGHT([CLIENT TABLE].ZIP,4),'') AS LastFour
FROM [CLIENT TABLE];

 

[Mike Hughes]

No not what I'm looking for. I want there to be a space (or a - ) between the first 5 numbers of the zip code and the last 4 numbers when there is a zip+4. I just want to know what I need to do to this query to accomplish that.
SELECT
RTrim([CLIENT TABLE].CITY)+' , '+
RTrim([CLIENT TABLE].ST)+' '+
RTrim([CLIENT TABLE].ZIP) AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+
RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER,
[CLIENT TABLE].PIN,
[CLIENT TABLE].A1,
[CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]
FROM [CLIENT TABLE];

 

[GolfProRM]

RTrim([CLIENT TABLE].CITY)+' , '+
RTrim([CLIENT TABLE].ST)+' '+
LEFT([CLIENT TABLE].ZIP,5) + 
IIF(LEN(RTRIM([CLIENT TABLE].ZIP)) > 7, '-' + RIGHT([CLIENT TABLE].ZIP,4),'') AS [ADD],

I believe you'd want to use pdx_man's approach and code it something like this (untested).

 

[Mike Hughes]

Well it kind of worked - this was one of the results

995153561-3561

I would like it to look like 99515-3561

 

[GolfProRM]

You did catch the LEFT function, right?

LEFT([CLIENT TABLE].ZIP,5)

 

[Mike Hughes]

This is what the query looks like now with the help of GolfProRM

SELECT
RTrim([CLIENT TABLE].CITY)+' , '+
RTrim([CLIENT TABLE].ST)+' '+
RTrim([CLIENT TABLE].ZIP)+IIf(LEN(RTRIM([CLIENT TABLE].ZIP))>7,'-'+RIGHT([CLIENT TABLE].ZIP,4),'') AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+
RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER, [CLIENT TABLE].PIN,
[CLIENT TABLE].A1, [CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]

FROM [CLIENT TABLE];

This is what it returns
ANCHORAGE , AK 995042268-2268

I want it to return ANCHORAGE , AK 99504-2268

Can anyone help?

 

[GolfProRM]

SELECT
RTrim([CLIENT TABLE].CITY)+' , '+
RTrim([CLIENT TABLE].ST)+' '+
[B]Left([CLIENT TABLE].ZIP,5)[/B]+IIf(LEN(RTRIM([CLIENT TABLE].ZIP))>7,'-'+RIGHT([CLIENT TABLE].ZIP,4),'') AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+
RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER, [CLIENT TABLE].PIN,
[CLIENT TABLE].A1, [CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]

FROM [CLIENT TABLE];

does that not work?

 

[pdx_man]

He's not listening (reading ...)

 

[Mike Hughes]

Sorry, went home sick. It works great! Thanks for all your help!