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