Fetching default record for multiple language

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 2008 Forums

Transact-SQL (2008)

Fetching default record for multiple language

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-16 : 06:54:06

Hi,

I have table with multiple language records in it. And default language will be english.

I have list of records of table for single language e.g. dutch.
For each row, if name is null or empty for dutch then that name should return with default language of english.

May I need to loop cursor to check this each row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 06:56:12

Is language stored as a field inside table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-16 : 06:59:38

Yes,


CREATE TABLE #names (ids int, names varchar(44), langcode varchar(10))
insert into #names values 
(1, 'en1', 'en'), (1, 'dt1', 'dt'), (1, '', 'gb'),
(2, 'en2', 'en'), (2, 'dt2', 'dt'), (2, 'gb2', 'gb')

select * from #names 
drop table #names

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 07:26:41

how will you determine which code corresponds to which language? is there a master table for that? Or does code always represent standard language codes available in sys.languages view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-16 : 07:39:21

I will pass language code in procedure. determined by country where site is opened.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 07:46:45

sorry you missed my point.My question was reg. how to find language details for records within table. There's no language field. If you want to determine language you need to have a field which stores language values in addition to codes which is there now.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-16 : 08:01:53

there is already field/column added in given example script.

langcode varchar(10))

That column holds language code and from application I will pass code and fetch records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 08:08:02

so is it always name for passed languagecode or return name for english?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-16 : 08:16:52

Yes, english is default one.

As per given table. You can see multiple language names entered. If I pass dutch language code and if I found 15 records then for each record if name null or empty then english name will returned.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-16 : 16:08:39

Here is one way:

select 
	a.ids,
	coalesce(nullif(a.names, ''), English.names) as names
from 
	#names as a
inner join
	#names as English
	on a.ids = English.ids
	and English.langcode = 'en'
	and a.langcode = 'gb'

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-05-17 : 02:08:48

Yes, Thanks.. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources