I need help parsing a varchar field in my SQL Server

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
mainrotorAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
select left(field,charindex('.',field) + 2) from yourtable;
0
 
Terry WoodsIT GuruCommented:
Did you mean to have some asterisks in your data?
0
 
GowthamNatarajanCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
sameer2010Commented:
@TerryAtOpus :)
0
 
mainrotorAuthor Commented:
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
 
stevejacob68Commented:
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
 
Anthony PerkinsCommented:
stevejacob68m

That syntax with TRUNCATE is not supported with T-SQL.
0
 
stevejacob68Commented:
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
 
Anthony PerkinsCommented:
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
 
stevejacob68Commented:
Hi,
This error occur because in there is alphabetic character in number. Remove alphabetic character and then run statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.