Parsing Characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parsing Characters

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-21 : 14:03:03

i have 3 tables..

table1:Batch Records
table2: Phone_versions
table3:targetsbuiltview

i have to join all the data in 3 tables,for the jobs started in the month of July, and common cloumns i have are "phonebuild" in table 2 and table 3,and i have a column "jobname" in table 1 which matches with phone build..(a part of it),so i have written the below query...

Select "all columsn needed are listed"
From ASWProductionDatabase.dbo.BatchRecords A INNER JOIN ASWProductionDatabase.dbo.PHONE_VERSIONS B

ON left(B.phonebuild,16)=left(a.jobname,16)JOIN ASWProductionDatabase.dbo.TargetsBuiltView C

ON B.PhoneBuild = C.PhoneBuild

Where A.JobName like 'M6801BFMBRLA6523%'and b.PhoneBuild='M6801BFMBRLA6523'

And YEAR(StartTime)='2009' and MONTH(StartTime)='07'

Order by StartTime,b.PhoneBuild
------------------------------------------------------------------here is my sample data

PhoneBuild Jobname
M6801BFMBRLA6523 M6801BFMBRLA6523_Blueprint_02JUL2009_071015pm
M6801BFMBRLA6523 M6801BFMBRLA6523_02JUL2009_033510pm
M6801BFMBRLA6523 M6801BFMBRLA652365_02JUL2009_033510pm

here i get the job name which is not for that particular build as i matched it with only 16chars,so is there any way where i can get only the jobname name which matches with that particular build?

And also i have to do this for each phonebuild ,is there a way where i can do this by gruoping with lengh of the chars or something..?

There are some 1000 of records for each build and i cant do this manually checking for each build...

Please Help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 14:41:23

Is there any way you (as a human) can look at a given row from table1 and know what specific phonebuild it is for? If so How?

based on your sample output it looks like if the next character after the PhoneBuild portion is an underscore then it is for that phonebuild. Could that be a consistent condition?

Be One with the Optimizer
TG

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-21 : 14:44:25

Yes thats the only condition i have matching the characters before _ with the phone build......

quote:
Originally posted by TG

Is there any way you (as a human) can look at a given row from table1 and know what specific phonebuild it is for? If so How?

based on your sample output it looks like if the next character after the PhoneBuild portion is an underscore then it is for that phonebuild. Could that be a consistent condition?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 14:54:59

try this:


From   ASWProductionDatabase.dbo.BatchRecords A 
INNER JOIN ASWProductionDatabase.dbo.PHONE_VERSIONS B 
       on charindex(B.phonebuild + '_', a.jobname) > 0 
JOIN   ASWProductionDatabase.dbo.TargetsBuiltView C
       ON B.PhoneBuild = C.PhoneBuild

Be One with the Optimizer
TG

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-21 : 16:13:29

could you please let me know what does 0 indicate here?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 16:24:27

take a look at charindex in Books Online.

It means that the b.phoneBuild + '_' was found somewhere in a.jobname

Be One with the Optimizer
TG

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-24 : 17:08:06

select "all fields"
From ASWProductionDatabase.dbo.BatchRecords A

INNER JOIN ASWProductionDatabase.dbo.PHONE_VERSIONS B

on charindex(B.phonebuild , a.jobname) > 0

JOIN ASWProductionDatabase.dbo.TargetsBuiltView C

ON B.PhoneBuild = C.PhoneBuild

Where YEAR(StartTime)='2009' and MONTH(StartTime)='07'and DAY(starttime)='3'

AND B.PhoneBuild LIKE 'Q6%'

Order by StartTime

Above is my query, how can i write a subquery in it with specific criteria

Here is my o/p:

PhoneBuild Jobname
Q6085BSNAVA500017 --- Q6085BSNAVA500017_14JUN2009_113134pm
Q6085BSNAVA500017S --- Q6085BSNAVA500017SBlueprint_14JUN2009
Q6085BSNAYA4373 --- Q6085BSNAYA4373_03JUL2009_002143am
Q6085CSNAARJT500017 --- Q6085CSNAARJT500017tun_14JUN2009

Expected o/p:
Q6085CSNAARJT500017 --- Q6085CSNAARJT500017tun_14JUN2009

I amm looking for the jobnames which are not separated by _ or blueprint word in them,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-24 : 17:57:42

Huh?
Your sample data AND sample for expected output they all have a _ - sign.

No, you're never too old to Yak'n'Roll if you're too young to die.

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-24 : 18:16:08

the o/p i have given is my current o/p and am looking for the o/p in my expected o/p, basically i want to filter my data, in my o/p i have the rows with _ and bluepint,in my expected o/p i only have one row where blueprint and _ is not separating the build name and date...

what am trying to do here is, i want list the jobs, as blue print and regular and i come across some other stuff which is neither blueprint nor regualar job separated by date, so want to have those jobs...which does belong to any category their have like some unecessary chars like "tun"

quote:
Originally posted by webfred

Huh?
Your sample data AND sample for expected output they all have a _ - sign.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources