Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parse field in SQL Server view

Posted on 2016-10-30
19
Medium Priority
?
73 Views
Last Modified: 2016-10-30
I was using this in one of my MS Access Queries but now I need to clone this in a SQL Server view. How can I change this to work in SQL Server? I was using this to parse some data from one of my fields in an Access query.

Val(IIf(InStr([fdesc],"-.")>0,Mid([fdesc],InStrRev([fdesc],"-.")+1,5),0)) As DiaTol

Open in new window

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 23

Expert Comment

by:yo_bee
ID: 41865884
Look at using Case When Then End. This is the equivalent to iif then else this.

https://msdn.microsoft.com/en-us/library/ms181765.aspx
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41865888
Here is a statement from a search in google.
This leverages LIKE to get the results of instr

CASE WHEN countries LIKE '%'+@selCountry+'%' THEN 'national' ELSE 'regional' END
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41865934
I need to extract those values, not use them as a criteria to toggle other values. The CASE WHEN won't work to extract the values.
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 23

Expert Comment

by:yo_bee
ID: 41865939
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41865957
Can you give us a before and after example of what you're trying to pull off here?

Access Iif is SQL Server CASE
Access InStr, Mid is SQL Server CHARINDEX

Here's an article that will get you much of the way there
Migrating your Access Queries to SQL Server Transact-SQL
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41865992
Here are a few examples. The values I am looking to parse won't always be 4 decimal places. They could be 3 or 4 decimal places. And they are not always at the end of the syntax either. But they will always have a -. to start that portion that I need to parse. If that part that I am looking to parse isn't in the syntax at all then I want it to return just a 0 as the result.

I need the -.0020 from this example: CCM-PLUS-RD-1.497-.0020
I need the -.0004 from this example: 316/316L-RD-0.2502-.0004-AR.0002  
I need the -.0010 from this example: 304/304L-0.094+/-.0010
I need the -.001 from this example: 17-4PH-RD-0.5005-.001
0
 
LVL 23

Accepted Solution

by:
yo_bee earned 2000 total points
ID: 41866061
Something like this
(Case when test like '%-.%' then SUBSTRING(test,CHARINDEX('-.',test)+2,5)
select (Case when test like '%-.%' then SUBSTRING(test,CHARINDEX('-.',test)+2,5) else null end) as test1,test,CHARINDEX('-.',test) as ex 
from Table1

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866070
That worked somewhat how I wanted it. I also need the -. in the "test1" result, not just the numbers after it. And it's not 5 decimals after the -. It can be 3 or 4 decimals. If it's 3 then just show the 3 decimals plus the -. and if it's 4 decimals then show the 4 decimals plus the -.

Here are a couple results from your code:

304SS-RD-0.0625-.0002X72.000       Shows 0002X in the test1 column. I should see -.0002 as the result
303SS-RD-0.2502-.0004-AR.0002      Shows 0004- in the test1 column. I should see -.0004 as the result
A2-RD-0.6253-.0006X96                     Shows 0006X in the test1 column. I should see -.0006 as the result
304SS-WELDED-RD-.8600-.0020      Shows 8600- in the test1 column. I should see -.0020 as the result

I should note that there could be other items that look similar to the syntax I am looking to parse as in the above example that returned 8600- as the result. IF the syntax comes right after either RD, HX, TU, FL, SQ then look for the next syntax after that. That is the diameter, not the tolerance. The tolerance syntax will always come after that.

And for results in test1 that DO NOT have that syntax in it I want to see a 0 instead of NULL.

Item: F068528 Line: 001            Shows NULL. I want to see 0, not NULL
0
 
LVL 23

Assisted Solution

by:yo_bee
yo_bee earned 2000 total points
ID: 41866092
Remove the +2 to get the '-.'
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866094
That worked for that but I am still getting NULL when it doesn't find anything. And I also need to get it to only grab the second syntax of that setup. I need it to skip the first set if it comes after the RD, HX, TU, FL, or SQ. See the 4th example in my previous post.
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41866106
That is what I scripted to do. What do you want to do if it does not match
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866108
If it doesn't match then have it put a 0 in the column test1
0
 
LVL 23

Assisted Solution

by:yo_bee
yo_bee earned 2000 total points
ID: 41866109
Change the NULL to 0
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866113
That worked. Just one last problem about it grabbing the first syntax instead of the second. This example shows how it's grabbing the wrong one:

304SS-WELDED-RD-0.8600-.0020        It should be showing -.0020 but it's showing -.8600. How can that be changed to skip the section right after the RD (or other 2 letter codes I listed)? That first section will have a 0 or another number to the left of the decimal. It will only have 1 digit to the left so is there a way to skip that section based on that and then grab the next syntax?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866125
Actually it won't grab the first section because there is a 0 right after the -. So no need to modify the code any further. I think it's complete just the way it is.
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41866131
Glad to help.  I would recommend one thing.  Rather than one of EE's work out the complete solution for you, you should look at what is given to you and if you need to tweak it you should see what is happening there and see if you can rework it for your needs.  I find that this helps really sink in rather than someone giving me the answer outright.

Good luck and I am here as well as many others to help in future questions.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866132
Thank you very much. I understand what you're saying. It's just SQL is not one of my strong points so I always struggle a lot with SQL. But I will take that approach going forward. Thank you again.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866273
I have a follow up question. I just noticed that there are a few records that have 6 decimals to the right of the decimal. So there could be some that have 3, 4, 5, or 6 decimals. But regardless of which amount of decimals the record has it might not be the last values in the field. There could be other values after that syntax ends so we can't make it static to grab 6 decimals all the time. How can this be done where it will grab either 3, 4, 5, or 6 decimals? I can say that the last decimal no matter if it's 3, 4, 5, or 6 will be greater than 0. Could that be something that can be used to modify the code to grab it no matter which amount of decimals it is?


Examples:

.001
.0001
.00008
.00015
.000025
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41866462
I suggest you create a new question.

But you coukd try this

select (Case when test like '%-.%' then SUBSTRING(test,CHARINDEX('-.',test),
LEN(test)
) else 0 end) as test1,test,CHARINDEX('-.',test) as ex
from Table1
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question