How To Return A Substring Based Upon A Character In The String ?

Apr 29, 2008

Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields:


select UserName from MyDirectory


NTSRJoe Meekleton
NTSRAnfreesa ClinklHead
BRYAmy Kooplosa
AKVermian Strikoos

How can I fix this query so that it returns everything to the right of the '' character?

So I want the query to instead return the following:

Joe Meekleton
Anfreesa ClinklHead
Amy Kooplosa
Vermian Strikoos

View 2 Replies


ADVERTISEMENT

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

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 :: 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

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

Search The Table Based On A Substring (was: Newbie Question)

Jul 30, 2006

Hello everyone,

Just getting started with MS SQL. I have the following question:

I have a table with one field being a string (it is also an index field). I want to search the table based on a substring. For instance:

if a Field in any record has a value of '1.2.3.4.5.6'
and I search for '1.2.3.'
I want to get that record

How?

Thanks,

Avi.

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

Select Where String Contains Substring

Oct 17, 2007

i'm using sql server 2005 and i need the sql i can use to select rows where the string contains a substring (in access i used instr but now it tells me it's not a built-in function.

View 4 Replies View Related

Query For Substring To Get Some Particular Value In A String (Get Querystring Value By Sql )

Jan 28, 2008

I have a column name URL in Table1 with data like  <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a>            <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a>  i need to grab the news_media_i value by sql query  Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance 

View 4 Replies View Related

Extract Substring From String(Regular Expressions)

Feb 11, 2008

Hello forum, I need extract a substring form a string that follows a regular expressions -->

T:1º/PQ:1/TALLA:2(MOD.51 100/150)

T:<number>º/PQ:<number>...

I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...

I would like me use it as:
SELECT RegularExpression (Field)

Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Use Substring And Charindex To Extract Desired String

Jul 9, 2014

I have a column that contains the follwoing string I need to compare.

ek/df/cv/
ek/df/cv/f

All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.

I can't do it by using the combination of Substring() and charindex() and Len()

View 9 Replies View Related

T-SQL (SS2K8) :: Trying To Use Substring (charindex) To Remove Start Of A String

Aug 26, 2014

Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.

An example string

declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2

I've tried

select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'

And it fails dismally...

View 8 Replies View Related

How To Return Character(s) After A Dash (-)

Mar 24, 2015

I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.

It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.

View 7 Replies View Related

Want To Return Character After A Dash

Mar 23, 2015

I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.

View 6 Replies View Related

Return Text To Chosen Character

Nov 1, 2005

how can i return text in a column up until a special character?

I have this:
abcd_1234
abcdef_123456

And i want this:
abcd
abcdef

How can i return everthing thats left (or right) of the '_' character? i have tried use LEFT/RIGHT and SUBSTRING but i just cant make it to work.

thx in advance //Mr

View 2 Replies View Related

How Can I Deal With Character Of Hard Return?

Jun 22, 2006

Hi there,

Can anybody tell me how the character of hard return expressed in SQL Server. For example if I wanna convert any hard return character in a varchar field to something else, like a space, how could I write Replace function? Or if I wanna search any hard return character, how could I write where clause?



thanks a lot

View 5 Replies View Related

String Index Function (substring / Charindex) - Extract Specific Characters From Data

Aug 5, 2013

SQL Query. What i need is to be able to extract specific characters from data.

Eg name

1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results

i.e. 1.2-KPIA1-App-00001 1.2 KPIA1

*I need this in part of a script as there is thousands of rows of data.

View 4 Replies View Related

Can I Split A Field Based On A Character?

Nov 9, 2005

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

View 5 Replies View Related

Find And Replace Carriage Return Character

May 30, 2007

Hi I have a text file which I need to import into Access or SQL.

It is 500,000 records which is pipe delimited. The problem is, is that it can contain carriage returns: (square symbol).

Therefore I need to find and replace these characters

Does anybody know of any free ware text file viewers that can do this??

Best Regareds

David

View 3 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

NTEXT Special Character SQUARE Appearing Where A Return Should Be. How Do I Fix This?

Sep 17, 2006

I had to import some FileMaker Data into SQL Server 2005 and in the resume field a "SQUARE" special character is appearing everyplace that a return should be.

It has really messed up the formatting. I know how to use the updatetext in a cursor to replace the special character but I don't know what to replace the "SQUARE" special character with so the text is displaying a new line rather then this "SQUARE" character.



Any help would be appreicated.

View 6 Replies View Related

SQL 2012 :: Eliminate Characters From A String Based On Another String?

Dec 17, 2014

I have a string 'ACDIPFJZ'

In my table one of the column has data like

PFAG
ABCDEFHJMPUYZ
KML
JC
RPF

My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.

My output should be:

PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)

View 2 Replies View Related

Find In String And Return Part Of String

Mar 21, 2007

I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

View 7 Replies View Related

SQL Get First Character Of A String

Feb 18, 2005

Is there a function in SQL that will find the first character of a character string?

Select Org from PR where id = '100'

Normally, Org will retuen a string such as '1:00'. But, all I want from this string is the first character, '1'.

Thank You

View 3 Replies View Related

String With 15,500 Character

Apr 12, 2007

Hi all,

I am currently doing a mapping from text file to sql table. there is one column in the text file that contain more than 15,000 characters in it. it causes me an error because i was using varchar data type, in which i believe can only store 8000 char. can anybody tell me which data type to use? and also, will i be able to change the data type in the input column also? if yes, which data type should i change it to?







Thanks,

Jul

View 3 Replies View Related

Simple Function For Returning A Character Based On Search Criteria..

Feb 20, 2007

Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen

View 2 Replies View Related

Reading Each Character In A String

Jun 4, 2004

I want to read a String - character by character.I mean If the string is 'SAMPLE'
then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality.
I hope I am clear.Is there a way that I can read it?

View 1 Replies View Related

Repeated Character In String

Jun 19, 2006

How can find out the repeated character in each row value of spacific column

View 1 Replies View Related

Parsing Character String

May 21, 2008

I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.



Here are the function and the stored procedure wher eit occurs:




CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))

RETURNS @Results TABLE (Item nvarchar(4000))

AS



BEGIN

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

-- ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0



BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Item) VALUES(@SLICE)

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

END

Procedure:


Code SnippetCREATE PROCEDURE [dbo].[RPTPatientAnalysis]

(

@stateList CHAR(2),

@employerIdList VARCHAR(4000),

@payerIdList VARCHAR(4000)

)

AS

SELECT

p.PAT_ID,

p.PAT_FirstName,

ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName,

p.PAT_LastName,

p.PAT_Gender,

CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB,

p.PAT_AddressStreet1,

ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2,

p.PAT_AddressCity,

p.PAT_AddressStateProvince,

p.PAT_AddressPostalCode,

ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress,

p.PAT_PhoneNumber,

ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name,

ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,

ISNULL(p.PAT_Comment,'') AS PAT_Comment,

ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID,

ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification,

ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName,

ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail,

ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission,

p.PAT_LastEandMCodingDateTime,

p.PAT_Desceased,

p.PAT_PCP_ID,

p.PAT_LastUpdatedDateTime,

ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType,

ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing,

ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal,

ISNULL(p.PAT_PortalID,0) AS PAT_PortalID,

ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name,

ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,

pcp.PRIMCP_ID,

ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID,

ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName,

ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname,

pcp.PRIMCP_DateDeactivated,

ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax,

ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice,

ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1,

ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2,

ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City,

ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State,

ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip,

ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1,

ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2,

ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City,

ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State,

ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip,

ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax,

pati.PATINS_InsuranceTypeID,

ISNULL(pati.PATINS_Account,'') AS PATINS_Account,

ISNULL(pati.PATINS_Group,'') AS PATINS_Group,

ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType,

ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount,

ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount,

ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays,

ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay,

ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay,

ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar,

ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan,

ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description,

ISNULL(ip.INSP_Name,'') AS INSP_Name,

ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice,

ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies,

CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate,

ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive,

CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate,

CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate


FROM dbo.patient AS p

LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID

LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID

LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID

LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1

LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID

LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID

WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,','))

AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,','))

AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))


Is there a faster / more efficient way to accomplish the above?

Any insight would be appreciated!!

View 9 Replies View Related

Adding Character In Between A String

Aug 17, 2012

Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.

View 2 Replies View Related

Separate String For Each Row By & Character

Oct 13, 2015

I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.

How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)

Example of data:

StudentID FunctionName Variables

1 Example1 Var1=10&Var2=xy&Sign=True&Role=False

View 2 Replies View Related

Update And Get First Character Of String

Dec 23, 2014

I have a column in SQL showing Patient Sex (Female/Male)

How Can I update the Column so that I can see only the first character of the string Female and Male( F and M)?

View 2 Replies View Related

Reversing A String Character

Mar 19, 2007

create a function to reverese a string pass it abcd and it returns dcba

you can't use the built in reverese function...

View 10 Replies View Related







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