Special char, how to deal with?

Question

Special char, how to deal with?

RightOnTarget

Hall of Fame

Points: 3041
More actions
May 19, 2014 at 12:34 pm

#291492

Hi all,
This character "ä" comes up in one of contacts last names.
It breaks one of our web apps. I'd like to deal with it in sql, if possible, but need your help, please.
I guess, I am not sure how to deal with it the "right" way. How about replace it with the "-" dash.
But, first, I guess, how do I identify this character?
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rightontarget (5/19/2014)
Hi all,
This character "ä" comes up in one of contacts last names.
It breaks one of our web apps. I'd like to deal with it in sql, if possible, but need your help, please.
I guess, I am not sure how to deal with it the "right" way. How about replace it with the "-" dash.
But, first, I guess, how do I identify this character?
Thanks,

Well the right way to deal with it is to modify your website so it can handle the characters stored by the users. The wrong way to deal with it is to change the data so the code works.

If you are deadset on doing it the wrong way you could handle it like this.

declare @LName nvarchar(10) = 'LästNäme'

select REPLACE(@LName, N'ä', '-')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Robert.J.Blackburn SSC Journeyman Points: 75 More actions · Answer 2

You can use REPLACE or CHARINDEX to see if it exists.

select 'ä', replace('lästname', 'ä', '-'), charindex('ä','lästname', 0)

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 3

Thanks for fast replies.

I am trying to identify other potential chars that may break my web app.

Why won't this work?

--drop table q

create table q (val1 varchar(1), val2 varchar(30))

insert into q(val1,val2) values ('ä','Eugene Aäbcdefg')

SELECT val1 FROM q WHERE val1 NOT LIKE '%[^a-zA-Z0-9]%' -- returns what I need

SELECT val2 FROM q WHERE val2 NOT LIKE '%[^a-zA-Z0-9]%' -- misses it

Thanks,

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 4

I found it. It's because of the space.

If I do the following, it works:

SELECT val2 FROM q WHERE replace(val2,' ','') NOT LIKE '%[^a-zA-Z]%'

Thanks,

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Glad you found a solution. I would still suggest that you should fix the web app to handle user data, not modify user data so that the website code can handle it. This issue should be on the developer side and not the sql side.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 6

I was looking at this as a learning opportunity and if fact learned something.

I will, however talk to a web developer.

Thanks again.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

rightontarget (5/19/2014)
I found it. It's because of the space.
If I do the following, it works:
SELECT val2 FROM q WHERE replace(val2,' ','') NOT LIKE '%[^a-zA-Z]%'
Thanks,

A better approach here would be to include a space in your regular expression.

SELECT val2 FROM q WHERE val2 NOT LIKE '%[^a-zA-Z0-9 ]%' -- no longer misses it

This keeps your code SARGable. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/