RANDOM_VARCHAR (TSQL Function)
Returns a random ascii string, which length is between the two values passed as arguments, inclusively.
Syntax
RANDOM_VARCHAR ( min_length , max_length )
Arguments
- min_length
- is the minimal length of the string, of type
int
. It must be a constant literal. - max_length
- is the maximal length of the string, of type
int
. It must be a constant literal.
Return Types
Returns varchar
.
Remarks
The returned string is lowercase, except the first character which is uppercase.
This function doesn’t exist in MS SQL Server.
You should use it to easily fill your test tables with random strings.
Examples
CREATE TABLE employees (
id INT NOT NULL IDENTITY(100, 1) PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NULL,
birthdate DATE NULL,
salary NUMERIC(12,2) NULL
);
GO
DECLARE @i INT = 0
SET NOCOUNT ON
BEGIN TRAN
WHILE @i < 100 -- insert 100 records
BEGIN
INSERT INTO employees (last_name, first_name, birthdate, salary)
VALUES (
UPPER(RANDOM_VARCHAR(5,10)),
RANDOM_VARCHAR(4,10),
IIF(RAND()<0.3, NULL, RANDOM_DATE('19500101', '20141231')), -- force NULL for 30% of records
RANDOM_FLOAT(3000,8000)
)
SET @i += 1
IF @i % 10000 = 0
PRINT 'records inserted: ' + CAST(@i as VARCHAR(100))
END
COMMIT
SET NOCOUNT OFF
PRINT ''
PRINT 'print 10 sample records:'
PRINT ''
SELECT TOP(10) * FROM employees
The result is:
id |last_name |first_name |birthdate |salary |
-----------+------------------+-------------------+----------+---------------+
100|XAQIMOXEA |Daqlev |1991-11-18| 6785.94|
101|OIWEBIKA |Isuhobe |1991-11-08| 6282.69|
102|WEZAXUX |Ozofekanoa |1959-07-03| 4276.35|
103|LFEDEA |Eper | <NULL>| 6604.12|
104|RONUMWOFEG |Otihuo |1989-08-09| 7765.29|
105|AAXOI |Xeux | <NULL>| 5230.39|
106|LOPUVU |Avuciijewi |1963-01-09| 5929.35|
107|UDOFIHEPM |Orediqatuu |1977-11-05| 4312.16|
108|UPUFUDPID |Ahoaluduku |1996-03-17| 5859.98|
109|HIVUC |Ilejepli | <NULL>| 3102.30|
(10 row(s) affected)
See also RANDOM_NUMERIC
.