SQL Server 2012 :: Parse A String Every 6 Letters

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)

INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

After parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 Replies View Related

SQL Server 2008 :: Using Left And Charindex To Parse String / Getting Rid Of Rest Of Data

Jun 16, 2015

I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins

update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1

that totally works.

update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2

gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.

View 2 Replies View Related

First Letters Of A String To Upper Case

Apr 23, 2002

Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional.
Thanks in advance for the help.


Php95saj

View 14 Replies View Related

Extract Numbers Or Letters From Mixed String

Mar 16, 2004

Yeah, it's pretty simple. Maybe it'll help someone out.


-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''

IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'

WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END

View 4 Replies View Related

Transact SQL :: How To Get Indexes Of Capital Letters In A String

Sep 15, 2015

Is it possible to pull the index(s) for capital words in a string.  For example, if I have a string that says, GetTableName, would it be possible to output, 0, 3, and 8?

View 5 Replies View Related

SQL Server 2012 :: Parse Two Delimited Table Columns Into Multiple Records

Oct 22, 2014

I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.

CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)

[Code] .....

The results I am looking to achieve are:

PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4

Is there an SQL statement that can be used to accomplish this?

View 1 Replies View Related

T-SQL (SS2K8) :: Find All Instances Of String That Contain Certain Letters - Escape Double Quote

Sep 14, 2015

I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.

Query example:

Select *
from myTable
Where item like '%FSC%'

This works great with the exception of when FSC is surrounded by double quotes.

ex: MySentencecontains"FSC"

I have tried Replace with no luck.

View 3 Replies View Related

SQL 2012 :: Swapping Drive Letters In Cluster

Jul 3, 2015

As part of a migration of data to a new SAN I have hit a bit of a snag in the migration. In summary what will happen is user database data files will be moved from one LUN (say drive F:) to a new LUN (say drive G:). Once all the data is migrated, plan is to remove dependency of that drive from SQL server and remove the drive and delete the LUN. So far, so good.

However one of the LUNs (drive D:) destined to be deleted also hosts the instance default directories, i.e. everything under MSSQL11.MSSQLSERVER (Data, Backups, FTData, JOBS, etc). BOL has articles on how to migrate system databases, including tempdb. But there is no guidance that I could find on how to relocate other folders. There are forums where users have listed registry changes, etc that can achieve this but these are steps I am unwilling to take on a production server.

So my plan is:
1) Add new drive to cluster (drive E:), sufficiently large enough to host instance default folders
2) Shutdown SQL server
3) Copy all default folders to new drive
4) Swap drive letters so that new drive is now D:
5) Start SQL server and if everything works, delete the original drive (which is now drive E:).

View 3 Replies View Related

Function To Parse A String

Mar 13, 2008

Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....

Here is the sample string: MFD-2fdr4.zip

CREATE FUNCTION Parse(String, '-' , '.')
.....
....
.....
END


and it shoud return 2fdr4

View 8 Replies View Related

Parse A String Using Charindex

Jan 15, 2008

Hi,

I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them.
The format of the name field is 'AAAA-BBBBBB-123'
here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id.
I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?


SELECT id AS 'ID',
name AS 'name',
sequence AS 'num'
FROM
FirstTable A
INNER JOIN SecondTable q
ON (CONVERT(INT,RIGHT(name,3))= a.id)
INNER JOIN ThridTable t
ON(t.id = q.id)
INNER JOIN FourthTable s
ON (q.name = s.name )
WHERE A.id = @ID
AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1))
ORDER BY 'ID','num'


One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?

Thanks,
Subha

View 9 Replies View Related

Nested CASE To Parse A String

Feb 6, 2006

I am attempting to create a view to bring together multiple joined tables based ona one to many relationship

Eg Table1 contains code, address
Table2 contains code, financial details
table3 contains code, financial history

This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2

My current stumbling block is:


One of the fields in table1 is a free format address field (address).
eg. could be (street addres, city prov, postal)
or
could be (street address 1, address2, address 3, city prov, postal)

I want to be able to assign individual components of the address to corresponding fields

if # of commas = 2 then
address1 = substring(address,1, position of first comma)
cityprov = substring(address,possition of first comma, position of second comman)
postal = substring(address rest of field)

I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...

ANy ideas on how best to accompish this?
( this table is needed for some leacy software which can only handle one record with all infor....

greg

View 2 Replies View Related

Parse Out All Email Addresses In A String

Jul 23, 2013

I have a string like this one in my column

Mike@yahoo.com, Bill@aol.com, Dan@yahoo.com, Frank@gmail.com

In my result set I need to display all email addresses that do not have the @yahoo.com domain.

View 7 Replies View Related

Parse A Numeric String From A Field

Jul 23, 2005

Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!

View 2 Replies View Related

Parse Long String To Columns

Dec 31, 2007



I have a column with varchar(2000) that contains events with time-stamps. The data looks something like this for a record:

03:14:46: abc 03:14:47: def 03:14:59: xyz 03:15:17: zzz

I would like to parse out each time-stamp and event to a separate columns, like

col1= 03:14:46: abc
col2= 03:14:47: def
col3= 03:14:59: xyz
col4= 03:15:17: zzz

The number of events are dynamic so number of time-stamps with events can be anything from 2-30 of them.

Anyone would suggest how I can resolve this?

Thanks.

View 5 Replies View Related

Trigger To Parse A String From Insert Statement

Mar 22, 2006

I have two tables:tb_news---story_id-productlisttb_lookup---story_id-productwhen an insert command is run on tb_news, productlist field ispopulated with a value such as 'abc, def, de'when this happens, i need tb_lookup to be populated with seperaterecords for each product in productlist and the story_id from tb_news.Example:INSERT INTO tb_news (story_id, product_list)VALUES (12345, 'abc, def, de')Results:tb_news--12345, 'abc, def, de'tb_lookup--12345, 'abc'12345, 'def'12345, 'de'Ideally, I would like this to use recursion and give me tha ability tochange the delimiter at any time (might not always be a comma). someproducts may have a period in them. number of products is unknown andmight be 0 (field may be empty or NULL).

View 2 Replies View Related

How To Parse A String Column With Comma Delimited

Jul 20, 2005

Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156

View 2 Replies View Related

Can You Parse Out A Text String &> 8000 Chars Long??

Mar 30, 2005

Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.

Say I have this string that is text data type...

'aaa|bbb|ccc|ddd|....'

..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Parse A String To Equal Length Sub-strings

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

after parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow..So the parsing should be dynamic.

View 2 Replies View Related

Stored Procedure To Parse Delimited String To Table?

Apr 10, 2015

SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.

Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.

Sample Strings - [RawData Table]
04102015_114830_10_013_9_8_6_99999_Test 1_1_0
04102015_115030_10_013_9_8_6_99999_Test 2_1_0

Desired Output - [WIP Table]

Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive
-----------------------------------------------------------------------------------
04102015 114830 10 13 9 8 6 99999 Test 1 1 1
04102015 115030 10 13 9 8 6 99999 Test 2 1 1

View 16 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.

View 9 Replies View Related

SQL 2012 :: Parse XML Data To Table Format

May 5, 2014

declare @xml table (xmldata xml)
insert @xml select
N'<parseObject name="Motel">
<fields>
<field name="vehicleno" fieldType="int" fieldSize="">

[Code] ....

I want to extract data in in table format

ParseObjectName FieldType FieldSizeGrammar
Motel VehicleNo Int NULL div.biz-page-subheader li > span.i-phone-biz_details-wrap
mapbox-icon span.biz-partno[/size]

View 2 Replies View Related

SQL 2012 :: Parse XML Files Into Relational Table

Sep 24, 2014

We have a project to parse out an xml file into relational sql table. The xml file is complex type with multiple nesting. We are trying to resort to use XQuery to parse it out to SQL tables-because of one thing or the other - other options on the table were not viable. I know that we can use C# to do the same thing but we are sticking to TSQL with Xquery. Has anybody used the same route for processing large complex xml files?

View 1 Replies View Related

SQL 2012 :: Parse Data Separated Through Text

Nov 10, 2014

I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).

ID ParseData
1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]
2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.]
3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]

New Data (abc1, abc2, abc3, etc each have a field in the new data set)
ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8
1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt
2 [abc1.Gray...abc8.] Gray Shoes Striped jacket
3 [abc1.Blue...abc8.] Blue Jeans

If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?

My code so far looks like:
DECLARE
@string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]',
@searchString1 varchar(20) = 'abc1',
@searchString2 varchar(20) = 'abc2';

SELECT newstring
FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);

This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?

View 1 Replies View Related

.NET Framework :: Generating PDF Letters From Server

Jul 9, 2015

Generating different types of letter (PDF formats) from SQL server?

View 3 Replies View Related

SQL Server 2012 :: SELECT A String

Dec 1, 2014

I have string as below:

InvoiceTemplateId= SOURCE.InvoiceTemplateId
,Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

I need a string Like below:

Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

So I need every thing except the First value before first comma .

View 3 Replies View Related

SQL Server 2012 :: More Than 1 Alphanumeric Chars In A String

Mar 12, 2014

I have an employee table

Surname GivenName
ABC x.yz
A.BC X.*YZ
A*.BC xyz

The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters. In this case the output will be

a.bc x.*yz
a*.BC xyz

I know how to find non alphanumeric chars in a string but not sure if I can how to find the strings with minimum 2 non alphanumeric chars?

View 9 Replies View Related

SQL Server 2012 :: Find Numbers From A String

Apr 8, 2014

I have a string and i want to get only the numbers from right.

For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.

I got a function where it gives me all the numbers in a string but I don't need that

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT

[Code] ....

If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select

SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.

View 8 Replies View Related

SQL Server 2012 :: Convert Parts Of String To Int

May 2, 2014

I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

And this is what I have so far

SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0

[Code] ...

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.

View 3 Replies View Related

SQL Server 2012 :: Extracting String Between Certain Characters

Aug 18, 2014

I need extracting string that is between certain characters that are in certain position.

Here is the DDL:

DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]

[Code] ....

What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.

Sample output would be:

KY117
CA132
OH174
MD163
FL191

I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.

View 5 Replies View Related

SQL Server 2012 :: Group By Part Of String

Jan 8, 2015

I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.

"GROUP BY LIKE '%ThePartToGroupBy%'"

View 9 Replies View Related

SQL Server 2012 :: Getting String Out Of Data From A Column?

May 7, 2015

I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below

Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information

sample data from system_id_information column

########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>

[Code] .....

I am looking output of two columns, which are bolded

product_id snumber
654081-B21 MXQ43905SW

for serial number this is common

before string :HostSystemIdentificationInfo"><identifierValue>

and after string </identifierValue><identifierType><label>Service tag

and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies

View 9 Replies View Related

SQL Server 2012 :: SET 1 Symbol Instead Whole String In Varchar Value

Jun 12, 2015

I have such Function:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'TwoDigitsNumber' AND type = 'FN')
DROP FUNCTION MinimumOFThree;
GO
CREATE FUNCTION TwoDigitsNumber(@a int)
RETURNS nvarchar(20)

[Code] ....

The only first letter 'f', 's', 'e' is inserted in value instead 'first', 'second', 'equal'.

Why ? How can i insert whole string

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved