Solved

I need help parsing a varchar field in my SQL Server

Posted on 2014-04-14
10
552 Views
Last Modified: 2014-05-19
Hi Experts,
I need help parsing a field in my SQL Server database.  Sample of  the data that I am trying to parse is as follows:

1334A1.184H5  334
435.37351AG

I want to parse the field so that it returns all the characters left of the asterisk, the asterisk, and only 2 characters right of the asterisk.  The above sample data should look as follows after it's been parsed:

1334A1.18
435.37

How can I do this?

Thanks in advance,
mrotor
0
Comment
Question by:mainrotor
[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
10 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40000846
Did you mean to have some asterisks in your data?
0
 
LVL 2

Expert Comment

by:GowthamNatarajan
ID: 40000856
Try this...  


declare @a varchar(10);
  set @a = '1334A1.184H5  334'
 
  select substring(@a,1,(CHARINDEX('.',@a)+2))

Instead variable @a you can also also use column name in  a table like below

 select substring(col_name,1,(CHARINDEX('.',col_name)+2)) from table_name
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 40001226
@TerryAtOpus :)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:mainrotor
ID: 40001564
TerryAtOpus,
Yes, I meant to have an asterisk in my example and then I changed it to a period '.' .  I guess I forgot to also change it in my wording.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 40001755
select left(field,charindex('.',field) + 2) from yourtable;
0
 
LVL 3

Expert Comment

by:stevejacob68
ID: 40071862
Hi,
Use this command to display the value after decimal
SELECT TRUNCATE(1334A1.184H5,2);
SELECT TRUNCATE(435.37351AG,2);
These command display only two digits after decimal.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40072510
stevejacob68m

That syntax with TRUNCATE is not supported with T-SQL.
0
 
LVL 3

Expert Comment

by:stevejacob68
ID: 40074385
HI,
If previous code is not working
try this one
SELECT CAST(ROUND(1334A1.184H5,2,1) AS DECIMAL(18,2));
SELECT CAST(ROUND(435.37351AG,2,1) AS DECIMAL(18,2));

this will definately solve your problem
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40074822
stevejacob68m,

This is error message in SSMS:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'A1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'AG'.
0
 
LVL 3

Expert Comment

by:stevejacob68
ID: 40074872
Hi,
This error occur because in there is alphabetic character in number. Remove alphabetic character and then run statement.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

738 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