December 10, 2012 at 4:26 am
create table emp
(
last_name varchar(50)
)
insert into emp
select abc_worldbaank
union
select xyzabc_countrybank
union
select yyybb_districtbank
union
select zzzaaa_internationalbank
my requirement is to display the text after '_'
worldbaank
countrybank
districkbank
internationalbank
I used two method
1) select substring(last_name, charindex('_',last_name), len(last_name) - charindex('_',last_name))
-- 4 functions
2) select right(last_name, len(last_name) - charindex('_',last_name))
-- 3 fucntions
is 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 ?
karthik
December 10, 2012 at 6:23 am
karthik M (12/10/2012)
create table emp(
last_name varchar(50)
)
insert into emp
select abc_worldbaank
union
select xyzabc_countrybank
union
select yyybb_districtbank
union
select zzzaaa_internationalbank
my requirement is to display the text after '_'
worldbaank
countrybank
districkbank
internationalbank
I used two method
1) select substring(last_name, charindex('_',last_name), len(last_name) - charindex('_',last_name))
-- 4 functions
2) select right(last_name, len(last_name) - charindex('_',last_name))
-- 3 fucntions
is 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 ?
Your two queries do no produce the same result.
Number 1 produces: -
--------------------------------------------------
_worldbaan
_countryban
_districtban
_internationalban
Number 2 produces: -
--------------------------------------------------
worldbaank
countrybank
districtbank
internationalbank
Which did you want?
Your create script doesn't work, here is a fixed version: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
CREATE TABLE #testEnvironment (last_name VARCHAR(50));
INSERT INTO #testEnvironment
SELECT last_name
FROM (VALUES ('abc_worldbaank'),
('xyzabc_countrybank'),
('yyybb_districtbank'),
('zzzaaa_internationalbank')
) a(last_name);
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: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
CREATE TABLE #testEnvironment (last_name VARCHAR(50));
INSERT INTO #testEnvironment
SELECT 'abc_worldbaank'
UNION ALL SELECT 'xyzabc_countrybank'
UNION ALL SELECT 'yyybb_districtbank'
UNION ALL SELECT 'zzzaaa_internationalbank';
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: -
SELECT SUBSTRING(last_name, CHARINDEX('_', last_name) + 1, 50)
FROM #testEnvironment;
Which produces: -
--------------------------------------------------
worldbaank
countrybank
districtbank
internationalbank
Or if the other format was correct, you can do this: -
SELECT SUBSTRING(last_name, CHARINDEX('_', last_name), 50)
FROM #testEnvironment;
Which produces this: -
--------------------------------------------------
_worldbaank
_countrybank
_districtbank
_internationalbank
December 10, 2012 at 9:59 am
--------------------------------------------------
worldbaank
countrybank
districtbank
internationalbank
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.
karthik
December 10, 2012 at 11:32 am
Assuming 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.
select parsename(replace(last_name,'_','.'),1)
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
select substring(last_name, charindex('_',last_name) + 1, len(last_name))
December 10, 2012 at 6:29 pm
any other approach which will resolve this issue by using only one function or without using any functions?
karthik
December 11, 2012 at 1:13 am
karthik M (12/10/2012)
any other approach which will resolve this issue by using only one function or without using any functions?
CLR. If you mean native, then no.
December 11, 2012 at 3:54 am
karthik M (12/10/2012)
any other approach which will resolve this issue by using only one function or without using any functions?
If you don't count the functions inside the function, this one:
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
CREATE TABLE #testEnvironment (last_name VARCHAR(50));
INSERT INTO #testEnvironment
SELECT last_name
FROM (VALUES ('abc_worldbaank'),
('xyzabc_countrybank'),
('yyybb_districtbank'),
('zzzaaa_internationalbank')
) a(last_name);
go
select
*
from
#testEnvironment
cross apply dbo.DelimitedSplit8K(last_name,'_')
where
ItemNumber = 2;
go
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
You will find the code for the dbo.DelimitedSplit8K function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply