SQL Server 2012 :: Find Number Of Times Character Appears In A String

Apr 11, 2015

In my staging table I am having data like below

ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯

Now I want to find the Number of times a '¯'character appears in a string. I should get output 14

View 2 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

To Find The Number Of Occurences Of A Particular Character In A String

Aug 8, 2001

Hi all,

@str varchar(500)

select @str = 'abcd,efgh,i,jklmn,opsqrtuv'

For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?

Thanks in advance,
-Praveena

View 1 Replies View Related

Returning How Many Times A Character Occurs In A String

Jul 13, 2004

I need to know how to figure out the number of times a character occurs within a string, in SQL.

For example, "happy" concerning "p" would be evaluated to 2.

View 2 Replies View Related

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 2012 :: Removing Duplicate Character From String

Oct 18, 2015

How to write a function to remove duplicated characters only if they come in sequence.

Examples

darrk should return dark

eeagle should return eagle

redd should return red

corner should corner as it is as the r's are not in sequence.

View 9 Replies View Related

SQL Server 2012 :: Split String Into Columns Based On Special Character

Dec 4, 2013

How to get the required result in SQL 2012

Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

My Expected Output

Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS

View 1 Replies View Related

SQL Server 2012 :: Replacing Recurring Characters In A String With Single Character

Jan 20, 2014

I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.

For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:

DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');

and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.

View 9 Replies View Related

SQL Server 2012 :: How To Find Continues Number Ranges

Dec 19, 2013

Consider the following data

create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')

[code]....

if status = 0 then unused status = 1 then used

i need to find 10 continues unused numbers

This is the sample data. In live I'm having plenty of data like that . So quite complex.

note : In real world I need to find the 1000 continues number ranges.

View 8 Replies View Related

Find The Occurrence Of A Particular Character Inside A String

Dec 5, 2007

Hi all,

Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.


For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.

Can this be acheived through any function.

Cheers !
ash

View 6 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

Concat With A Loop - Convert Number Into String Character

Apr 5, 2014

I'm new to SQL and I've been trying this for a while now.

I have let's say a loop of numbers from one to ten. It appears like this:

1
2
3
4
5

What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.

View 7 Replies View Related

SQL Server 2012 :: Adding A Number To A String To Create Series

Sep 3, 2014

add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:

312061
312062
312063
312064

How can I check what the EventID is, then concatenate a sequence number by the EventID?

View 9 Replies View Related

SQL Server 2012 :: Query To Find Distinct Multiple Instances Of A Pattern In A String?

Apr 30, 2015

One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.

Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].

I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above

[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]

I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.

Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.

DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.

[code].....

View 7 Replies View Related

SQL Server 2012 :: Find A Specific String And Replace It With Another Inside Of VARCHAR Field

Aug 14, 2015

I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.

UPDATEtbl
SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%

'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.

View 5 Replies View Related

SQL 2012 :: Picking Number String Out Of Text String

Jul 14, 2015

I have a text field which has entries of variable length of the form:

"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"

I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:

1234
123
12389

the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.

View 5 Replies View Related

SQL Server 2014 :: ERD - Minimum Number Of Times That R2 Can Appear In Connection?

Dec 14, 2014

I got this ERD and I have 2 questions about it:

What is the minimum number of times that R2 can appear in the connection? and what is the maximum?

A=5
B=5
C1=1
C2=2

View 1 Replies View Related

SQL Server 2008 :: Tell Number Of Times People Use SSMS To Change Client Data?

Mar 21, 2015

I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?

View 6 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

SQL Server 2014 :: Find String With Spaces And Replace Them With Other String

Sep 8, 2015

I have following query which return me SP/Views and Functions script using:

select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function

I need its result as:

Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function

I used following

select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

View 5 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Difference Between Two Date / Times And Flag Each Hour In Between

Dec 31, 2013

I have a request where i would like to get the start date/time and end date/time and flag (with an int) which hours (24 hour clock) have values between the two dates. Example car comes into service on 2013-12-25 at 0800 and leaves 2013-12-25 at 1400 the difference is 6 hours and i need my table to show

Column: Hour_6 Value: 0
Column: Hour_7 Value: 0
Column: Hour_8 Value: 1
Column: Hour_9 Value: 1
Column: Hour_10 Value: 1
Column: Hour_11 Value: 1
Column: Hour_12 Value: 1
Column: Hour_13 Value: 1
Column: Hour_14 Value: 0

As i'm working away at it i'm trying to figure out how i could use a Time Dimension table for this but dont really see much. So far i have the difference between the two times in hours (hour_diff) and the start hour (min_hour) so i would like to do something where i update the first hour (min_hour) and update columns based on the numbers of hours (hour_diff)

View 9 Replies View Related

SQL 2012 :: Find Out Number Of Columns In Flat File Before Process That Particular File

Apr 14, 2014

I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.

@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like

Product_20120607060930.txt
Product_20130708060930.txt

[code]....

Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.

View 4 Replies View Related

SQL Server 2012 :: Date / Time Calculations For Work-plan Times?

May 31, 2015

I have a table that stores working hrs, such as

RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000

So working times can go over midnight, there can be more than one working period in a day etc.

For this staff member the summary of the weeks work will be

18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs

Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.

So for example

If this person

had a days holiday on the 22nd, shown in the HOLIDAY table as

StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000

A Leave of Absence on the 20th, shown in the LEAVE table as

StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000

And was off sick on the morning of the 19th, shown in the SICKNESS Table as

StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000

Now the Summary table should now show

18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs

The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?

I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.

View 1 Replies View Related

SQL Server 2008 :: Finding Multiple Character Occurrence In String

Mar 24, 2015

I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.

This is for a data cleanup.

CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')

SELECT * FROM #Names
DROP TABLE #Names

View 9 Replies View Related

String Or Binary Data Would Be Truncated. (only For 1700 Character String?)

Nov 2, 2006

I am trying to insert a row into a table of Microsoft SQL Server 2000.

There are various columns.















[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-



StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset

      at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)


Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?

Can anyone please tell me what's wrong?

View 6 Replies View Related

How To Find The Difference Between 2 Times In SQL

Apr 10, 2007

Hi,

I am trying to find the difference between two times (timeIN, timeOut)and then populate a new field called "duration of stay" with the difference.

The two times are in a table called attendance and the new duration of stay field is to be populated in a table called factAttend2

Is there an easy way to do this?

Thanks

View 5 Replies View Related

SQL Server 2012 :: Character Value Count From Dataset

Oct 31, 2014

Consider following script

create table data_set (id int primary key, col1 varchar(10))
go
insert into data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')
select * from data_set

I tried this below

Declare @child_ids int
@col_val varchar
@count int

select @child_ids, @col_val, @count, count(col1) as records from data_set
group by col1
order by col1

But I want my output like below .....

View 2 Replies View Related

SQL Server 2012 :: Tab Character In Query Results

Jul 9, 2015

I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!

select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID

[Code] ....

Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00

View 9 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

Finding Number Of Hours Between Two Times?

Apr 24, 2014

I need to write a SQL to find number of hours between a begin time and end time. The fields are varchar. There are several date functions in sql, but I am not able to figure out how to get the hours between two times that is not in date format.

BEGIN_TIME END_TIME
0900----- 1500
1000----- 1700
1000----- 1230
0930----- 1030

View 3 Replies View Related

Count The Number Of Times A Row Satisfies CONTAINS

Sep 15, 2006

Is there a way to get the number of occurances of a word within a specific row?

I want to query a dataset and have a column act as the number of occurances of a word within that row. I also need to use Full-Text searching... Is this possible?

View 6 Replies View Related

Count How Many Times Column Is Populated With A Number Per ID

Jul 10, 2013

What i want to do is count how many times the [Omnipay_Account] column is populated with a number per parentid.

In the [Omnipay_Account] column you can either have a null or a 15 digit number

Idea result layout would be three columns these columns would be

Every row, would need to be grouped via the parentid number

ParentId Omnipay MSIP

My query is

SELECT
[ParentID]
,[Omnipay_Account]
FROM [FDMS].[dbo].[Dim_Outlet]

View 3 Replies View Related







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