InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 14:57:56 GMT20http://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspx[quote][b]karthik M (12/10/2012)[/b][hr]any other approach which will resolve this issue by using only one function or without using any functions?[/quote]If you don't count the functions inside the function, this one:[code="sql"]IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END;CREATE TABLE #testEnvironment (last_name VARCHAR(50));INSERT INTO #testEnvironmentSELECT last_nameFROM (VALUES ('abc_worldbaank'), ('xyzabc_countrybank'), ('yyybb_districtbank'), ('zzzaaa_internationalbank') ) a(last_name);goselect *from #testEnvironment cross apply dbo.DelimitedSplit8K(last_name,'_')where ItemNumber = 2;goIF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END;[/code]You will find the code for the dbo.DelimitedSplit8K function here: [b][url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url][/b].Tue, 11 Dec 2012 03:54:58 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspx[quote][b]karthik M (12/10/2012)[/b][hr]any other approach which will resolve this issue by using only one function or without using any functions?[/quote]CLR. If you mean native, then no.Tue, 11 Dec 2012 01:13:13 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspxany other approach which will resolve this issue by using only one function or without using any functions?Mon, 10 Dec 2012 18:29:13 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspxAssuming that your fields are always as described you could use. However this probably won't perform as well as the 3 and 4 function methods. [code="sql"]select parsename(replace(last_name,'_','.'),1)[/code]With the substring method you don't have to get the exact length of the remaining string, so you could do the following if you are really against hard coding a length[code="sql"]select substring(last_name, charindex('_',last_name) + 1, len(last_name))[/code]Mon, 10 Dec 2012 11:32:27 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspx[quote]--------------------------------------------------worldbaankcountrybankdistrictbankinternationalbank[/quote]y expected output:sorry..as i was rush from the office to reach home, i didn't use " " to the string.But I don't want to hardcode the maximum length.Mon, 10 Dec 2012 09:59:28 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspx[quote][b]karthik M (12/10/2012)[/b][hr]create table emp(last_name varchar(50))insert into empselect abc_worldbaankunionselect xyzabc_countrybankunionselect yyybb_districtbankunionselect zzzaaa_internationalbankmy requirement is to display the text after '_'worldbaankcountrybankdistrickbankinternationalbankI used two method1) select substring(last_name, charindex('_',last_name), len(last_name) - charindex('_',last_name)) -- 4 functions2) select right(last_name, len(last_name) - charindex('_',last_name)) -- 3 fucntionsis it possible to do this by using 2 or only one string function ?or else is it possible to do without using any string funtion ?[/quote]Your two queries do no produce the same result.Number 1 produces: -[code="plain"]--------------------------------------------------_worldbaan_countryban_districtban_internationalban[/code]Number 2 produces: -[code="plain"]--------------------------------------------------worldbaankcountrybankdistrictbankinternationalbank[/code]Which did you want?Your create script doesn't work, here is a fixed version: -[code="sql"]IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END;CREATE TABLE #testEnvironment (last_name VARCHAR(50));INSERT INTO #testEnvironmentSELECT last_nameFROM (VALUES ('abc_worldbaank'), ('xyzabc_countrybank'), ('yyybb_districtbank'), ('zzzaaa_internationalbank') ) a(last_name);[/code]You could also have written it like this, if you want it to be accessable for those not running SQL Server 2008 or SQL Server 2012: -[code="sql"]IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END;CREATE TABLE #testEnvironment (last_name VARCHAR(50));INSERT INTO #testEnvironmentSELECT 'abc_worldbaank'UNION ALL SELECT 'xyzabc_countrybank'UNION ALL SELECT 'yyybb_districtbank'UNION ALL SELECT 'zzzaaa_internationalbank';[/code]If you want to use only two functions, you'll have to rely on the fact that you know the data-type is VARCHAR(50).So, since you know the size is 50 at maximum, you can do this: -[code="sql"]SELECT SUBSTRING(last_name, CHARINDEX('_', last_name) + 1, 50)FROM #testEnvironment;[/code]Which produces: -[code="plain"]--------------------------------------------------worldbaankcountrybankdistrictbankinternationalbank[/code]Or if the other format was correct, you can do this: -[code="sql"]SELECT SUBSTRING(last_name, CHARINDEX('_', last_name), 50)FROM #testEnvironment;[/code]Which produces this: -[code="plain"]--------------------------------------------------_worldbaank_countrybank_districtbank_internationalbank[/code]Mon, 10 Dec 2012 06:23:59 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1394540-392-1.aspxcreate table emp(last_name varchar(50))insert into empselect abc_worldbaankunionselect xyzabc_countrybankunionselect yyybb_districtbankunionselect zzzaaa_internationalbankmy requirement is to display the text after '_'worldbaankcountrybankdistrickbankinternationalbankI used two method1) select substring(last_name, charindex('_',last_name), len(last_name) - charindex('_',last_name)) -- 4 functions2) select right(last_name, len(last_name) - charindex('_',last_name)) -- 3 fucntionsis it possible to do this by using 2 or only one string function ?or else is it possible to do without using any string funtion ?Mon, 10 Dec 2012 04:26:34 GMTkarthik M