Concatenate Character To Random-id

Mar 19, 2008

I am creating a random-id in this way:

declare

@rand_id int
set @rand_id=(SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))

print @rand_id

 

now I want to add "-07" to the end of the random number generated.

if 12345 is the random number, i want it to be 1245-07.

Any idea how to concatenate the "07" to this random number?

View 4 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Concatenate Random Date And Time

Oct 20, 2015

I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:

select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate,
CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTimeBut when I try to make it a single datetime field:

select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)

Which returns with: Conversion failed when converting date and/or time from character string.

So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...

View 9 Replies View Related

Returning Random Records And NOT Similar (random Questions)

Jul 20, 2005

Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke

View 1 Replies View Related

URGENT - Random 10 From Random 20 (2 Tbls)

Oct 14, 2004

I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.

Help is greatly appreciated.

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

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

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

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

Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character

Feb 1, 2008

Hi!

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks

int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}

message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());

View 1 Replies View Related

How To Concatenate

Feb 1, 2007

Need help. How to concat into 1 row from the example below:

Table Address
Block Flr Road
2 1 Green
3 - Maine
4 3 Bell
5 2 Prade

Result :
Block 2 Flr 1 Green Road
Block 3 Maine Road
Block 4 Flr 3 Bell Road
Block 5 Flr 2 Prade Road

Cheers!

View 7 Replies View Related

How To Concatenate Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all 

View 5 Replies View Related

Concatenate Int && Var Char - SQL

Oct 1, 2007

Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result  Thanks 

View 2 Replies View Related

Concatenate Variable

Feb 23, 2001

I'm trying to use a variable (@sqlfields1) for building a select statement.
Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":

If @showo1 = 1
select @sqlfields1 = 'o1,'

If @showo5 = 1
select @sqlfields1 = @sqlfields1 + 'o5'

I want @sqlfields1 to be = 'o1, o5'.

Regards,

Abrazame

View 2 Replies View Related

Concatenate String

Oct 25, 2004

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)

View 3 Replies View Related

Is Possible To Concatenate A Column?

Jul 27, 2007

Hey all

Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?

Thanks
tibor

View 14 Replies View Related

How To Concatenate 2 Recordsets

Aug 31, 2007

is it possible to concatenate 2 recordsets ?

i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.

if i use union, all the results will be mixed.

thanks for the help!

View 7 Replies View Related

Group By Concatenate Without Using XML

Sep 29, 2012

Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?

Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...

For example, if my data was:
1 "A"
1 "B"
1 "C"
2 "D"
2 "E"

I want to group by the first value and concatenate the second value:
1 "A,B,C"
2 "D,E"

Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .

View 2 Replies View Related

How To Concatenate Columns Into One Row

Mar 30, 2014

I have a table like so

code remarks
1234 abcd
4567 efgh

I need to concatenate the values in the remarks column into one row like below

abcd, efgh....how is it possible

View 4 Replies View Related

Query To Concatenate

Jan 25, 2007

I have a table like below.

certid name
1 xxx
1 yyy
1 cvb

now I want to output as xxx,yyy,cvb when i pass input certid=1
I want to get it using a single query statement

View 7 Replies View Related

How To Concatenate A String To A Int Value

Mar 5, 2007

I hope this is the correct place to ask this...

I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:

SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL

But unfortunately I am getting the error:
"Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."

Any idea how to get around this at all just using an SQL query statement?

View 2 Replies View Related

How To Concatenate Records Without UDF

Mar 27, 2007

-- Prepare sample data
DECLARE@Sample TABLE (ID INT, Code VARCHAR(3))

INSERT@Sample
SELECT290780, 'LT' UNION ALL
SELECT290780, 'AY' UNION ALL
SELECT290781, 'ILS' UNION ALL
SELECT290780, 'AY'

-- Show the expected output
SELECT DISTINCTs1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM@Sample AS s1
ORDER BYs1.ID

SELECT DISTINCTs1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM@Sample AS s1
ORDER BYs1.ID

SELECT DISTINCTs1.ID,
STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM@Sample AS s1
ORDER BYs1.ID

Peter Larsson
Helsingborg, Sweden

View 20 Replies View Related

Concatenate Where Clause

Jul 23, 2005

I am beggining to learn stored procedures and I am trying toconcatenate the where clause below but I keep getting an error:declare @sqlwhere varchar(15)set @sqlwhere = 'parentid=2'select * from categories where @sqlwhereThis is the error I am gettingServer: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '@sqlwhere'.What am I doing wrong?ThanksRod

View 6 Replies View Related

Concatenate Two Column.

Jul 20, 2005

hi.Trying to concatenate two columns:select uname+' '+uaddress as NameAdr from tblUserI only get the first field, name!!??The datatype is both nvarchar. 100 and 50 chars long.If I run this query:select uname, uaddress from tblUsertheres no problem.Any suggestions?ThanksSWN

View 2 Replies View Related

Concatenate Dateparts

Oct 9, 2007

Hi there,


We are storing dates in a SQL table as dayPublished, monthPublished, yearPublished because sometimes not all the values are available.

I thought something like

select dbo.Resources.monthPublished + '/' + dbo.Resources.dayPublished + '/' + dbo.Resources.yearPublished as fullDate

but it doesn't like my slashes, and if I leave them out it simply adds the three fields together. Boo!

Any ideas?

Thanks,
Megan

View 3 Replies View Related

Concatenate 2 Sql Strings

May 2, 2008

Hi,
Whats wrong with the following T-SQL?

DECLARE @SQL NCHAR(4000)

SELECT @SQL = 'xxxx'
SELECT @SQL = @SQL + ' yyyy'

PRINT @SQL

I'm only seeing 'xxxx' in the debug window so it appears that its not adding the 2 strings?
Weird?!?

View 8 Replies View Related

Concatenate At The End Of File

Jan 16, 2008

Hi,

I have a big problem, as I commented in other thread in this forum I Have a DataReader with an MDX query which right now runs out of memory, but I figured that if I made the query by range I could get what I need without running out of memory. However, to achieve that I need to concatenate at the end of a file with a Flat File destination, the result of every query, but I don't know how to do it, It always writes over the data.

View 8 Replies View Related

Concatenate Rows

Sep 20, 2007

Hi

I have a table similar to the following:

Date ID Name Job Number JobType
12/12/2007 123456 Fred Smith 111111 Full Day
12/12/2007 654321 Bob Blue 222222 Half Day AM
12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:
Date ID Name Job Number JobType
12/12/2007 123456 Fred Smith 111111 Full Day
12/12/2007 654321 Bob Blue 222222 Half Day AM
12/12/2007 654321 Bob Blue 333333 Half Day PM



Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

View 9 Replies View Related

Concatenate Files

Mar 4, 2008



Hi All,
I have a very simple question which I can't seem to find an easy way of doing in SSIS. (Alternatively I may be having a "Can't see the wood for the trees" moment )

I have a folder where files are deposited on a regular basis . (Every few seconds)

I want to take those files and concatenate them into a single file in another directory ready for processing.

The equivelent command prompt line would be copy c: estfiles*.* c: estfilescatteddata.dat

How would I be best doing this in SSIS? Also I could do with working out the best way to quiesce the folder so I don't get any locked files errors.

Any ideas?

Thanks

Nick

View 7 Replies View Related

Concatenate 2 Fields

Feb 13, 2008

Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks

View 7 Replies View Related

Concatenate SQL String...

Feb 20, 2008

Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.






Code Snippet

UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'

However attempting the same command in T-SQL I get the following error:

Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.

Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.

So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?

Thanks for the help, Onam

View 6 Replies View Related

Transact SQL :: Concatenate IDs Into One Row For Each Name

May 13, 2015

I have the below problem:

DECLARE @t TABLE
(
ID int, 
Name nvarchar(255)
)
INSERT INTO @t
SELECT 1, 'Raven'

[Code] ....

Returns the below:

ID     Name
1       Raven
4       Raven
43     Jack

Any names which are appearing twice I would like to have only one row so concatenate the IDs into one row for each Name, like below:

ID         Name
1, 4      Raven
43        Jack

Is this possible?

View 4 Replies View Related

Concatenate XML In Stored Procedure

Oct 28, 2007

Hi everyone,
 I'm writing a stored procedure where I need to concatenate two XML strings.
I would normally write XMLx = XMLy + XMLz
However because the variables are in XML SQL2005 doesn't like this and if I convert them to strings the XML structure gets lost.
Anyone any ideas ?
Thanks
D.

View 1 Replies View Related

Concatenate Multiple Rows In One Row

Mar 11, 2008

i have a table

View 1 Replies View Related







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