I see what went wrong. The site converted the xml space tag into a space.:w00t:

Other than that, could you explain the output that you need for "O'Gara^^#D"?

You're not sending enough parameters to your stored procedure. You need to either execute your procedure with all the parameters or assign defaults to your parameters. Even better would be...

You just need this:

SELECT REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), ' ' , '.'), 3), CHAR(7), '.') City,

REPLACE( PARSENAME( REPLACE( REPLACE( regstratn6, '.', CHAR(7)), '...

Erin Ramsay (10/9/2014)

---

To avoid results like O'GaraD you're going to have to employ logic such as (possibly):

Any characters that occur BEORE or AFTER an invalid character are also explicitly invalid....

Freefall99 (10/9/2014)

---

Luis Cazares (10/9/2014)

---

Your best option would be to sort the results in the front end.

Using a scalar function is a great performance problem.

Using Dynamic code can be safe if...

No, I created the CTE with the sample data to have something to run the code on.

You should use your table instead of the CTE.

You're welcome.

The most important part here is that you understand the code, what it does, how and why it works.

If you have any questions, feel free to ask. If something...

Your best option would be to sort the results in the front end.

Using a scalar function is a great performance problem.

Using Dynamic code can be safe if used correctly.

Does this article gives you an idea?

http://www.sqlservercentral.com/articles/T-SQL/62867/

This isn't tested at all because you didn't post all the tables involved nor sample data. The method used is described in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

SELECT E.[Part No#],

...

Or maybe you want your own version for anything that I can't see in your post.

WITH CTE AS(

SELECT * ,

...

You're posting on a 2014 forum so maybe other alternatives are available.

Here's one option.

WITH CTE AS(

SELECT * ,

...

This is an option using PARSENAME and some REPLACEs.

WITH SampleData(regstratn6) AS( SELECT

'ABERDEEN SD 57401-3580' UNION ALL SELECT

'ABINGDON VA 24210-5011' UNION ALL SELECT

...

GilaMonster (10/9/2014)

---

I won't, it sounds like an awful nightmare for the person in charge.

I wasn't expecting that someone would expose their SQL...

If you can't do it the right way, you could try an alternative.

This trigger needs a table to store the allowed IPs. Be sure on what you're doing or you...