urgent help with SQL query please

Question

Post reply

urgent help with SQL query please

Lucky9

Default port

Points: 1470
More actions
February 7, 2012 at 4:24 pm

#254972

I have a table with below structure
Create table TestClient
(ID int,
InternalCode nvarchar(100))
Insert into TestClient values(1,'abctest12-2-1-12345-12-01-08')
Insert into TestClient values(2,'xyztest24-1-2-2345-2013-1-5')
Insert into Testclient values(3,'QRStest69-2-3-34568-05-1-09')
Insert into Testclient values(4,'TUVtest99-9-8-56789-2011-9-10')
From the first record i want to retrieve 12345,Second record i want to retrieve 2345,Third record i want to retrieve 34568 and 4th record i want to retrieve 56789.
basically the last 3 parts belongs to date divided by month,day and year they might be any order(DD-MM-YY or YEAR-DD-YY) but they are divided by hyphens(-) and i want to retrieve the ID which is 4 digit or 5 digit in the middle. I mean before the date part( for example : first record has the date part as 12-01-08 and second record has the date part as 2013-1-5)
Could someone please help me with the query. thanks a lot for the help.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Like so:

Create table #TestClient

(ID int,

InternalCode nvarchar(100))

Insert into #TestClient values(1,'abctest12-2-1-12345-12-01-08')

Insert into #TestClient values(2,'xyztest24-1-2-2345-2013-1-5')

Insert into #TestClient values(3,'QRStest69-2-3-34568-05-1-09')

Insert into #TestClient values(4,'TUVtest99-9-8-56789-2011-9-10')

select * from #testclient

select SUBSTRING( InternalCode,

CHARINDEX( '-', InternalCode,

CHARINDEX( '-', InternalCode)+1)+1) + 1,

CHARINDEX( '-', InternalCode,

CHARINDEX( '-', InternalCode)+1)+1)+1)

- CHARINDEX( '-', InternalCode,

CHARINDEX( '-', InternalCode,

CHARINDEX( '-', InternalCode)+1)+1) - 1)

from #testclient

If you wanted to get fancy about it you could build yourself some kind of splitter function, but the direct method seems to work faster if you know exactly which component you want and you're only after one of the pieces.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Here is another alternative. Kraig posted his while I was typing up my solution.

DECLARE @TestClient table

(ID int,

InternalCode nvarchar(100))

DECLARE @PartTwo TABLE (ID Int, subID Int, Somevalue nvarchar(100))

Insert into @TestClient values(1,'abctest12-2-1-12345-12-01-08')

Insert into @TestClient values(2,'xyztest24-1-2-2345-2013-1-5')

Insert into @TestClient values(3,'QRStest69-2-3-34568-05-1-09')

Insert into @TestClient values(4,'TUVtest99-9-8-56789-2011-9-10')

INSERT INTO @PartTwo (ID,

subID,

Somevalue)

SELECT tc.id,ss.itemnumber,ss.item

FROM @TestClient tc

CROSS APPLY AdminDB_Test.dbo.stringsplitter(tc.InternalCode,'-') ss

SELECT ID,[4] AS DesiredStrenSegment

FROM (SELECT ID,subID,SomeValue

FROM @PartTwo

) B

PIVOT (MIN(SomeValue) FOR subID IN ([4])) AS P

Note that I use a string splitter to populate a second table. From there, I retrieve the value for the 4th slot and pivot the results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lucky9 Default port Points: 1470 More actions · Answer 3

Thanks a lot for the quick reply Evil Kraig.

I have tested the query provided by you it works fine for the first and third record but the output for second record i get as 1 instead of 2345

and for the fourth record i get the ouput as 9 instead of 56789.

There might records also with the values (5,'RTYhdf789,9-6-45678-2011-01-11')

I want to get retrieve the string 45678

Could you please help me to solve the query.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Lucky9 (2/7/2012)
Thanks a lot for the quick reply Evil Kraig.
I have tested the query provided by you it works fine for the first and third record but the output for second record i get as 1 instead of 2345
and for the fourth record i get the ouput as 9 instead of 56789.

My result set:

----------------------------------------------------------------------------------------------------

12345

2345

34568

56789

I'm not sure how you get the incorrect information running that as is. Is that a direct copy/paste of my code or was it modified?

There might records also with the values (5,'RTYhdf789,9-6-45678-2011-01-11')
I want to get retrieve the string 45678
Could you please help me to solve the query.

Then you're basically SOL. With variable concatonators you don't know which to use when. I don't have a method other than building a string splitter function that could handle multiple parameters, which I don't have handy. That's dirty data and nothing 'quick' enough for an urgent issue is going to fix dirty data.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Lucky9 (2/7/2012)
Thanks a lot for the quick reply Evil Kraig.
I have tested the query provided by you it works fine for the first and third record but the output for second record i get as 1 instead of 2345
and for the fourth record i get the ouput as 9 instead of 56789.
There might records also with the values (5,'RTYhdf789,9-6-45678-2011-01-11')
I want to get retrieve the string 45678
Could you please help me to solve the query.

If a comma is the only thing that will be used to replace a dash, just add a REPLACE to the code already offered and Bob's your uncle. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 6

Sample data:

DECLARE @test-2 AS TABLE

(

ID integer PRIMARY KEY,

InternalCode nvarchar(100) NOT NULL

);

INSERT @test-2

(ID, InternalCode)

VALUES

(1, N'abctest12-2-1-12345-12-01-08'),

(2, N'xyztest24-1-2-2345-2013-1-5'),

(3, N'QRStest69-2-3-34568-05-1-09'),

(4, N'TUVtest99-9-8-56789-2011-9-10');

Possible solution:

SELECT

t.ID,

t.InternalCode,

ExtractedID = LEFT(f3.String, f4.pos)

FROM @test-2 AS t

CROSS APPLY (SELECT SUBSTRING(t.InternalCode, 1 + PATINDEX(N'%[^0-9a-z]%', t.InternalCode), LEN(t.InternalCode))) AS f1 (String)

CROSS APPLY (SELECT SUBSTRING(f1.String, 1 + PATINDEX(N'%[^0-9a-z]%', f1.String), LEN(f1.String))) AS f2 (String)

CROSS APPLY (SELECT SUBSTRING(f2.String, 1 + PATINDEX(N'%[^0-9a-z]%', f2.String), LEN(f2.String))) AS f3 (String)

CROSS APPLY (SELECT PATINDEX(N'%[^0-9a-z]%', f3.String) - 1) AS f4 (pos);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

SQL Kiwi (2/7/2012)
Sample data:
DECLARE @test-2 AS TABLE
(
ID integer PRIMARY KEY,
InternalCode nvarchar(100) NOT NULL
);
INSERT @test-2
(ID, InternalCode)
VALUES
(1, N'abctest12-2-1-12345-12-01-08'),
(2, N'xyztest24-1-2-2345-2013-1-5'),
(3, N'QRStest69-2-3-34568-05-1-09'),
(4, N'TUVtest99-9-8-56789-2011-9-10');
Possible solution:
SELECT
t.ID,
t.InternalCode,
ExtractedID = LEFT(f3.String, f4.pos)
FROM @test-2 AS t
CROSS APPLY (SELECT SUBSTRING(t.InternalCode, 1 + PATINDEX(N'%[^0-9a-z]%', t.InternalCode), LEN(t.InternalCode))) AS f1 (String)
CROSS APPLY (SELECT SUBSTRING(f1.String, 1 + PATINDEX(N'%[^0-9a-z]%', f1.String), LEN(f1.String))) AS f2 (String)
CROSS APPLY (SELECT SUBSTRING(f2.String, 1 + PATINDEX(N'%[^0-9a-z]%', f2.String), LEN(f2.String))) AS f3 (String)
CROSS APPLY (SELECT PATINDEX(N'%[^0-9a-z]%', f3.String) - 1) AS f4 (pos);

Simpler?

SELECT

t.ID,

t.InternalCode,

ExtractedID = CASE WHEN PATINDEX(N'%[0-9][0-9][0-9][0-9][0-9]%',InternalCode) > 0

THEN SUBSTRING(InternalCode,PATINDEX(N'%[0-9][0-9][0-9][0-9][0-9]%',InternalCode),5)

WHEN PATINDEX(N'%[0-9][0-9][0-9][0-9]%',InternalCode) > 0

THEN SUBSTRING(InternalCode,PATINDEX(N'%[0-9][0-9][0-9][0-9]%',InternalCode),4)

END

FROM @test-2 AS t

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Paul White SSC Guru Points: 150467 More actions · Answer 8

Mark-101232 (2/8/2012)
Simpler?

Yes. It's answering a slightly different question, but it's definitely less code.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi