Solved

I need help parsing a varchar field in my SQL Server

Posted on 2014-04-14
10
553 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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