Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

4 functions, 3 functions...2 & 1 is possible? Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 4:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
Post #1394540
Posted Monday, December 10, 2012 6:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 2,379, Visits: 7,579
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



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1394574
Posted Monday, December 10, 2012 9:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521

--------------------------------------------------
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
Post #1394695
Posted Monday, December 10, 2012 11:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 1,054, Visits: 3,123
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))

Post #1394722
Posted Monday, December 10, 2012 6:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
any other approach which will resolve this issue by using only one function or without using any functions?

karthik
Post #1394843
Posted Tuesday, December 11, 2012 1:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 2,379, Visits: 7,579
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.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1394926
Posted Tuesday, December 11, 2012 3:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
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/.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1395014
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse