Solved

I need help parsing a varchar field in my SQL Server

Posted on 2014-04-14
10
535 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
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
 

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 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 40001755
select left(field,charindex('.',field) + 2) from yourtable;
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now