Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

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
0
mainrotor
Asked:
mainrotor
1 Solution
 
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
 
sameer2010Commented:
@TerryAtOpus :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
awking00Commented:
select left(field,charindex('.',field) + 2) from yourtable;
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now