June 5, 2015 at 1:13 pm
Hi Everyone,
I have a database in which sample data is as below:
सुविधा देवी पुत्री हजारू राम
देवकी देवी पुत्री शंकर दास
लीला देवी पुत्री काँशी राम
मनोरमा उर्फ सपना देवी पुत्री बिद्या सागर
Now I need to saprate the name and father name on the basis to relation mentioned in the string e.g.
Name Relation Fathar
सुविधा देवी पुत्री हजारू राम
देवकी देवी पुत्री शंकर दास
लीला देवी पुत्री काँशी राम
मनोरमा उर्फ सपना देवी पुत्री बिद्या सागर
Can anyone guide me how can I do the same in SQL Query.
June 5, 2015 at 1:32 pm
I don't know what language your data is in, but it's not English, and I can't read it. Please post some DDL that has CREATE statements for your table or tables, along with INSERT statements for some sample data, and the expected results (all in English please, so that most forum readers can actually read it). What you posted may be readable by you, but probably not by most folks on the forum.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 8, 2015 at 6:12 am
The enterd data i.e. posted is in Hindi language i.e. local language in India.
June 8, 2015 at 6:28 am
Let me say up front that names are difficult. What you have to do is to figure out a rule you can use to split the name into its various parts. The rule will have to work with all situations you encounter. First, middle, last, hyphenation, spaces, apostrophes, etc. You also have to handle "last, first" versus "first last". I don't know Hindi at all, so I can't tell you if this is even possible in your language, but I can tell you that it's very difficult to do in English.
If you can figure out a rule, see the link in my signature to Jeff Moden's string splitter. It'll split strings based on a single character and is very fast. If the rules you come up with are more complicated than a single character as a delimiter, then you might be able to use some of the techniques in the article to code a solution. Either way, I wouldn't expect that this is going to be simple.
Of course, the best way to handle names is to collect the different parts of the name in separate columns. Then you can concatenate them however you want for reporting.
June 8, 2015 at 9:06 am
This solution works for the supplied data, but I don't guarantee that it will work for all cases.
I've assumed that you will create a table containing relationship words that will be used to join on the strings to split them. I've used a CTE to create a temporary version of this table.
I've also assumed that the relationships are separate words, so I've made sure to test for a space on either side of the relationship.
;
WITH Names(nm) AS (
SELECT *
FROM (
VALUES(N'?????? ???? ?????? ????? ???')
, (N'????? ???? ?????? ???? ???')
, (N'???? ???? ?????? ????? ???')
, (N'?????? ???? ???? ???? ?????? ?????? ????')
) AS N(nm)
)
,
Relations(rel) AS (
SELECT rel
FROM (VALUES( N'??????')) AS r(rel)
)
SELECT LEFT(n.nm, ci.num) AS [Name], r.rel AS Relation, SUBSTRING(n.nm, ci.num + LEN(r.rel) + 2, LEN(n.nm)) AS Father
FROM Names AS n
INNER JOIN Relations AS r
ON n.nm LIKE '% ' + r.rel + ' %'
CROSS APPLY (
SELECT CHARINDEX(' ' + r.rel + ' ', n.nm)
) AS ci(num)
Jeff Moden's splitter is probably overkill in this instance, because it is meant to split variable length lists into separate records. It sounds like your records have a set pattern of "name relationship name", so it's not a variable length list and you probably want to separate it into separate fields rather than separate records.
Drew
PS: I used Google translate to translate your text into English and then made assumptions based on the very limited data that you have provided. Non-native speakers of Hindi (which the vast majority of this forum are) can't be expected to know anything about Hindi. The solution provided can only be as good as the data provided, because the forum users have no general knowledge of Hindi to fill in the extensive gaps in your data.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply