Transact SQL :: Get Substring Of File Name Up To Last Underscore
Apr 22, 2015
I have filenames and I want to get a substring of the file name up to the last underscore if it exists and if not up to the last period.
Create table FileNames (fileName varchar(40))
insert into FileNames values ('this_is_a_long_filename.ext')
insert into FileNames values ('this_is_an_even_longer_filename.ext')
insert into FileNames values ('short_filename.ext')
insert into FileNames values ('supershort.ext')
What I want returned is:
this_is_a_long
this_is_an_even_longer
short
supershort
View 5 Replies
ADVERTISEMENT
Dec 1, 2015
is it possible to identify which value is causing me the above error message and how to resolve it,These are for British postcodes.
create table #tmp (postcode varchar(200) NULL)
insert into #tmp values ('NULL')
insert into #tmp values ('-')
insert into #tmp values ('.')
insert into #tmp values ('0L6 7TP')
insert into #tmp values ('AB10 1WP')
insert into #tmp values ('AB51 5HH')
[code]...
This is the main query
select postcode,LEFT([Postcode], CHARINDEX(' ',[Postcode]) - 1)
from #tmp
order by Postcode
drop table #tmp
View 4 Replies
View Related
Sep 11, 2013
I have data that looks like this:
CAT_DOG
MICE_CAT
RABBIT_DOG
LION_MICE
I want a select clause that will give me just this:
CAT
MICE
RABBIT
LION
View 5 Replies
View Related
Oct 9, 2000
Hi friends,
I got troubles when I tried to pass table name to sp_columns via
@local_variable:
declare @tname as char(50)
set @tname = 'TB_MyTable'
exec sp_columns @tname
result of such script is:
TABLE_QUALIFIER ...[skipped]
-----------------------------------
(0 row(s) affected)
But if I will say:
exec sp_columns 'TB_MyTable'
result will be correct (all neccessary data about columns will be provided)
So, looks like if I pass table name which contains underscore via
@local_variable or SP parameter - result will be wrong.
If table name won't contains underscore - everything works fine and result
of script:
declare @tname as char(50)
set @tname = 'MyTable'
exec sp_columns @tname
will be absolutely correct.
Please, anybody can clarify this situation ???
Thanks in advance,
Michael
View 2 Replies
View Related
Aug 17, 2004
I'm attempting to doing a search of the db for all name entries with the underscore character "_".
I use %_% and get all the entries.
What am I doing wrong?
Help .... thanks!
View 2 Replies
View Related
Jul 23, 2005
Hello All,DDL Statements:CREATE TABLE [dbo].[Table1] ([MyDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOI have a varchar column which represents dates in YYYYMMDD and MM/DD/YYformats. If I query using:SELECT MyDate FROM Table1WHERE DOB LIKE '________'Why aren't the dates in MM/DD/YY returned ? Is the / a specialcharacter in T-SQL ?Thanks in advance
View 1 Replies
View Related
Mar 21, 2005
In MS Sql 2000 - is it possible to find the literal underscore character (_). Something like:
SELECT * FROM foo WHERE bar like 'a\_%'
where the query would return "a_abc", "a_cba", and "a_lksdfjlkdsjlksjdfl", but not "abc"?
I've tried:
SELECT * FROM foo WHERE bar like 'a\_%'
SELECT * FROM foo WHERE bar like ( 'a' + CHAR(95) + '%' )
Any other suggestions?
View 1 Replies
View Related
Jun 25, 2014
I have made a stored procedure, it makes successfully when created, but when I open it in modify, there is a red underscore under the procedure name.
When I place the courser over the red underscore, the massage is "Invalid object name"
View 1 Replies
View Related
Jan 8, 1999
I want to establish logging using NT Security. The problem is that user names in NT have underscore(_). When I establish NT Scurity in SQL security manager, SQL Server tries to add blank names as login id and they will not go through. Is it manadatory to remove underscore from username?
Because of structrual hierarchy, I have different groups in NT to be mapped to SQL Server groups and each group has different permission on SQL tables. In some cases we might have same users in more than one group. I read that it is not possible if you use NT integrated security. Is there any thing that can be worked around this and is this problem still exists in SQL 7.
Thank you in advance
Ali Malekshahi
View 3 Replies
View Related
Apr 14, 2008
Hi Gurus,
How do I write a T-SQL function for a string before a underscore is found?
Thanks Gurus.
View 2 Replies
View Related
Sep 22, 2015
I have data like ABC_Tablename_12345, i just want everything before and after _ also if there is no underscore in the name just return the name.
View 2 Replies
View Related
Jun 19, 2008
Hello,
I want to search a column with all the words deliminate by underscore. E.g. User_id, Community_name, author_id and etc.
It seems like freetext only deal with string with blank deliminator. How should I do the rull text search on column like this? Here is the code.declare @var varchar(2000)
set @var = 'id'
select [name], definition,version_code
from dbo.base
where freetext([name],@var)
thx
View 3 Replies
View Related
Aug 24, 2015
I have the following SQL
IFOBJECT_ID('tempdb..#TempTable_Lockbox_File_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_File_Header_Record
;
IFOBJECT_ID('tempdb..#TempTable_Lockbox_Batch_Header_Record')IS NOTNULLDROP TABLE#TempTable_Lockbox_Batch_Header_Record
;
[code]....
When I send my query results to a file in SQL Server Management Studio, how come I'm seeing the following in Notepad++? FH TEST "FH" which I thought should be in a CHAR(2) data column is there but "TEST" seems to start in Column 6...not column 3 as I would have expected. I was expecting... FHTEST.
View 3 Replies
View Related
Aug 27, 2015
In my SSIS Package, I have to write my [FileHeaderRecord] row, then my [BatchHeaderRecord] row, then my details. How can I do this in a SQL Server Query? When I try my SSIS, my file looks like so..
FHTEST 00000208262015 BH000208262015
I want my BH, Batch Header data, to appear on a new row in the file.Do I have to build a dynamic query to do this?Is there any trick in SSIS to do something like this?I did try creating separate Data Flow Tasks to Query the [FileHeaderRecord] and then use a Flat File Destination and then another Data Flow Task to Query the [BatchHeaderRecord] and use a Flat File Destination again NOT overwriting the file.
View 2 Replies
View Related
May 5, 2015
I want to send a file from <g class="gr_ gr_25 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="25" id="25">sql</g> to different server via FTP:
I found following script on internet: [URL] ...
When I run this. I get following error:
Invalid command.
Invalid command.
Not connected.
open Ftp.server.com
Username
Password
ls C:/Users/Desktop/test/ e: este: est est.txt
quit
NULL
View 2 Replies
View Related
Feb 19, 2013
I am using SELECT FOR XML which is working great.My problem is writing the results out to the filesystem.I am using spWriteStringToFile procedure that uses Scripting.FileSystemObject to write the file.The file gets written and all of the "xml" is there, but there are no CR/LFs and parsers, browsers and validators don't like it.What can I do to get a more usable output file?
View 11 Replies
View Related
Feb 19, 2004
Do U know how to unzip a file with T/SQL?
thanks in advance,
View 6 Replies
View Related
Sep 16, 2015
After running a query (from the Query Builder) in SQL Server 2008 sometimes I can right-click on the results pane and "Save results as CSV file", other times it's not an option. After running a query for 24 hours (several million record results) I can't seem to do anything with the results. I have my settings:
Options | Query results | SQL Server | Default Destination for Results
set to "Results to File" and a path entered, but it doesn't work. Is there wording I can add to the end of my SQL statement such as "TO FILE xxx.csv" or something?
View 4 Replies
View Related
May 11, 2015
The code in cmd looks like today.
sqlcmd -S PC03 -d db_test -E -o "testMyData.csv" ^
-Q "[test2]" ^
-W -w 999 -s","
I would like to change the name of the file into "20150512". The name of the file should be today's date. I do not know how to do it.
View 6 Replies
View Related
Oct 5, 2015
I have 4 different queries in one SSMS New query window that are returning expected results in 4 resultsets. However I want to output these results to a single .js file one after the other in the order of queries. Is that possible?
View 7 Replies
View Related
May 27, 2009
I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?
View 21 Replies
View Related
May 16, 2012
I need fmt(format ) file for below values
“Stuid”,”Stuname”,”Class”,”DOJ”,”English”,”Math”,”Science”
"S1","Ram","10/31/2011,Monday",40,32,50
"S2","Bala","10/31/2011,Monday",50,45,69
"S3","Sam","10/31/2011,Monday",74,78,79
"S4","Jon","10/31/2011,Monday",65,58,89
"S5","Jos","10/31/2011,Monday",41,25,69
"S6","Jim","10/31/2011,Monday",74,41,41
"S7","Jack","10/31/2011,Monday",98,57,47
"S8","Sate","10/31/2011,Monday",87,73,45
"S9","Brb","10/31/2011,Monday",47,89,65
"S10","Jom","10/31/2011,Monday",14,100,47
View 15 Replies
View Related
Oct 5, 2015
I am working on SQL Server 2008 and there is a problem in my SQL Server log file. I am seeing this from last 8 days, my log file size increasing continuously while i have set it on Simple Recovery model. Is there any method from which I can reduce the size of my log file and also I want to know, how the Recovery models affect to the size of a log file?
View 4 Replies
View Related
May 11, 2015
I want to add some name of the column's header from stored procedure to the CSV file
bcp "EXEC [databasname].[storedprocedure]" queryout C:estbcp_outputTable.csv -c -T -SPC01 -r
I'm using the code above in CMD.
Unfortunately, I cannot change the code that is inside of the stored procedure because it is already used in productionphase.
View 5 Replies
View Related
Apr 24, 2015
I am curious what the "best practice" is for exporting data programmaticly from SQL Server to Excel. Is it best to do it straight from SQL Server, or should I do it with in my C# code? My program is going to pull the data, put in the excel file, then email the file. So I could write an SP that gets the data and puts it in the file, then have the C# code run the SP and email the file; Or I could have the code do everything, pull the data, export it & email it.
If it is considered better to have the SP do it, why and what is the best way? ROWSET functions?
View 4 Replies
View Related
Oct 21, 2015
As the title indicates am i searching for a method to export values of a column to a file (plain text). I have been searching on the internet about it but can't seem to find any information. I am working with Microsoft SQL Server Management Studio
The values has to come out as they are in the table meaning
aaaa
------
bbbb
------
ccc
-----
to
aaaa
bbb
ccc
I am hoping there is a method to do this by creating a script, telling what table to copy and to save it on file yyy/xxx/zzz.txt
View 13 Replies
View Related
Sep 14, 2015
I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.
What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.
View 5 Replies
View Related
Nov 6, 2015
I had a small requirement i had a table with embedded attachments and attachments are uploaded to the table and saved in varbinary field from front end. Want i want is i want to download particular embedded files in that table within SQL Server itself. I have tried googling but none of seems to be within SQl Server T-SQL. all are linked to Asp.NET how i can do?
View 6 Replies
View Related
Nov 5, 2010
I have a CSV file that I am trying to bulk load into a temp table. The data in the file is all jumbled together, as in, there does not appear to be a row terminator. However, I do see a bunch of little rectangular boxes that I assume are the row terminators.
When I run the bulk insert, the data is treated as one string. For example... If I have 10 columns in the table, the 10 columns will be populated, but the remainder of the data is dumped into the last column.
Here are the row terminators I have used so far that haven't worked.
,
,
,
, CRLF,
View 31 Replies
View Related
Jul 29, 2015
Copy out all data from a DB table into/across delimited text file(s) ensuring that each text file size is no more than 3MB.Have created a SSIS solution where it achieves this requirement ..well sort of achieves the requirement ... Here it what the current solution (sparing the minute details) does in a nutshell & Problems with it:
1) Created a function (Script below) which finds the
maximum row size in bytes in a given DB table & uses it to calculate
how many rows can be copied out into a text file without exceeding 3MB size limit.
For instance: A DB table selected had 788 rows in total and this function for this particular table returned a value of 181 rows { select [dbo].[udf_GetRowPartitionNumber](‘<TableName>’)as #ofRowstoPartitionTableby --181} meaning in order to not exceed the requirement of 3MB per text file, we had to copy all the data from DB table across (create) 5 text files {Select
CEILING(788
[code]....
View 4 Replies
View Related
Sep 27, 2015
I need to have a script that will restore the backup file in a folder but the backup file will have the date appended to it and I won't know what that date is.
View 3 Replies
View Related
Oct 28, 2006
Can anyone explaing briefly, why would this error appears???
Thank you
View 6 Replies
View Related
Aug 20, 2015
I would have to handover my DB with only Primary tables to client as part of SLA.
I am planning to keep these primary tables on a secondary file-group but how-ever, I will still have my procedures on primary file-group.
How can I accomplish this with client having no exposure to my stored procedures.
View 2 Replies
View Related
