Remove non english names - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Remove non english names

Author

Topic

BillEdd
Starting Member

12 Posts

Posted - 2011-08-19 : 10:54:05

Hello all. I am a new subscriber and therefore hope I am posting my question to the right forum, use the right terminology, and use proper forum etiquette. If not then someone please let me know. Additionally, I am somewhat of a SQL newbie and therefore please post answers with that in mind. Now, on to my question.

I have two name fields in a table, one that has English names and one that has Japanese names. Some records will have names in both fields and some will just have English with no Japanese names. I need to get just the English names and populate another table with those names The data is imported into the table from EXCEL files and those files have the Japanese names/characters you can “see” in the EXCEL files.

Currently I am just combining the fields into one as I populate the new table and then was going to CLEAN, STRIP, etc the garbage, which I know from limited experience is not real efficient – I should do that with my SELECT statement as I insert the records. The Japanese names are appearing as non-printable characters (the little empty squares) after the data has been imported.

So, how do I do this? TIA for any help.

Bill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 10:57:31

if you need to just get english names isnt just matter of only including the englishname field in select query used to insert?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 11:06:20

Something like this?


SELECT EnglishName
FROM MyTable
WHERE JapaneseName IS NULL OR RTrim(JapaneseName) = ''

BillEdd
Starting Member

12 Posts

Posted - 2011-08-19 : 11:13:58

Now that I think about it, yes that will work but I need to modify my question. Cust_Name will have the Japanese names in it. ALT_Cust_Name will have its equivalent English name it so I could just pull the names from the ALT_Cust_Name field but in some cases ALT_Cust_Name will be blank. When I encounter that I will need to pull the name from Cust_Name because it will contain the English name.

So, in VB Script logic:
If only the Cust_Name is blank then get the name from the ALT_Cust_Name field. If both Cust_Name and ALT_Cust_Name have names then get the name from the Cust_Name field.

In which case the SELECT statement could be written to handle that but I am not sure how to do that. Again, thanks for your help.

Bill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 11:25:02

[code]
SELECT COALESCE(ALT_Cust_Name,Cust_Name)
FROM MyTable
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BillEdd
Starting Member

12 Posts

Posted - 2011-08-19 : 13:58:23

Thanks again for all who have replied but it is still not working and here is why now. ALT_Cust_Name is not blank and nor is it NULL. When there is no name at all in that field then it has been padded with "space bar characters".

So, back to my way of thinkng using VB Script logic:
If only the Cust_Name is blank (when trimmed of "space bar characters") then get the name from the ALT_Cust_Name field. If both Cust_Name and ALT_Cust_Name have names then get the name from the Cust_Name field.

I am thinking the SELECT statement could be written to handle that but I am not sure how to do that. I need to have "clean" cust names. Again, thanks very much for your help.

Bill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:40:05

[code]SELECT COALESCE(NULLIF(REPLACE(LTRIM(RTRIM(Cust_Name)),CHAR(160),''),''),ALT_Cust_Name)
FROM MyTable
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources